Define Named Ranges in Excel Before Executing Queries

Once you’ve established a connection to Microsoft Excel using OLE DB, you’re not out of the woods. You still have to define a Named Range in Excel; then you can treat this named range like a database table to perform queries against. To do this from Excel, select the range of cells you want to represent the table?column headers in the first row?then choose Name | Define from the Insert menu to bring up the Define Name dialog. Choose a name for your table and click on OK. Be sure to have valid column names or they will be renamed for you in the recordset or table you bring into your application.

As a different approach, you might wish to do things through VB code. Using the Excel 8.0 object as a reference, this example takes a file specified by the FileName string and creates a named range whose name is specified by the variable TableName. This example chooses the used portion of the first sheet as the table range:

 Public Sub MakeExcelTable(FileName As String, _	Tablename As String)	Dim BookXL As Excel.Workbook	Dim RangeXL As Excel.Range	Dim SheetXL As Excel.Worksheet	Set BookXL = GetObject(FileName)	With BookXL		Set SheetXL = BookXL.ActiveSheet		Set RangeXL = SheetXL.UsedRange  		' Selects the entire used range of the first sheet		.Names.Add TableName, RangeXL		.Windows.item(1).Visible = True		.Save	End With	Set BookXL = NothingEnd Sub
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: