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