Programming Excel with VBA and .NET

Working with QueryTable Objects

The QueryTable object gives you programmatic access to the database queries that are native to Excel. These database queries let you retrieve data from a variety of data sources and insert the data into your worksheets. In the Excel interface, you create a database query by clicking Import External Data, New Database Query on the Data menu.

In code, you create a database query by adding a QueryTable object to the QueryTables collection. When you do this, you supply a connection string to your data source as well as a destination on your worksheet where you want the results of the query to be inserted. For example, the following code inserts information for a specific product from the Products table of the Northwind Traders sample database into the current worksheet, starting with the first cell of the worksheet:

Dim strConn As String
Dim strSQL As String
Dim qt As QueryTable
 
strConn = "ODBC;DSN=MS Access Database;" & _
    "DBQ=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;"
 
Set qt = ActiveSheet.QueryTables.Add(Connection:=strConn, _
    Destination:=ActiveSheet.Range("A1"))
qt.CommandText = "SELECT * FROM Products WHERE (Products.ProductID=10)"
qt.Refresh

You can also use the ADO or DAO programming interfaces to create a recordset, and use the resulting Recordset object as your data source. To use either of these programming interfaces in Excel, you need to add a reference to the appropriate object library. On the Tools menu in ...