Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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