An Excel VBA QueryTable Tutorial

Visual Basic for Applications (VBA) provide you with a VB language to manipulate Microsoft Office data such as the data stored in Excel. The "QueryTable" function lets you retrieve a list of data from an Excel spreadsheet. You must create a connection to the Excel spreadsheet, call the QueryTable function and use the data to display data to the user.

Instructions

1. Click the Windows "Start" button and select "All Programs." Click "Microsoft Office," then click the Office application you want to use to connect to the Excel spreadsheet.

2. Click the "Developer" ribbon tab, then click the "Visual Basic" button on the left side of the ribbon. This button opens the VBA editor.

3. Create the QueryTable variable and create the query string you want to use. The code below shows you how to create these VBA items and uses a query that selects customers on the spreadsheet.

   Dim tab As QueryTable

   query = "select * from sheet1.customers"

4. Create the Excel spreadsheet connection. The following code is an example of a connection to the "myfile.xls" spreadsheet.

   connection = "ODBC;DSN=myfile.xls;UID=;PWD=;Database=sheet1"

5. Query the spreadsheet for the data. The following code displays a list of data from the Excel QueryTable connection.

   With ActiveSheet.QueryTables.Add(Connection:=connection, Sql:=query)

   .Refresh

   End With

Tips & Warnings

• If the "Developer" tab does not appear on the ribbon, you may need to turn it on in Excel preferences.

References

• Microsoft: QueryTables Collection Object [Excel 2003 VBA Language Reference]