Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Jun 29, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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
	End With
	Set BookXL = Nothing
End Sub
Michael J.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date