Query Table Associated with a List Object

Standalone QueryTables are good for retrieving data that will be used for background or display purposes only. If you want the user to be able to interact with the data after it has been retrieved, a better option is to create a QueryTable associated with a ListObject. This creates a table in the Excel user interface with all of the built-in ease-of-manipulation features that users need to work with the data.

Note that there is some overlap between the QueryTable and ListObject properties and methods. For example, both the QueryTable and ListObject have a Refresh method that updates their data. When there is duplication, which object's property or method you decide to use is a matter of preference. Because this chapter focuses on QueryTables, the QueryTable properties and methods are used wherever there is duplication between the two object models.

The code for creating a QueryTable associated with a ListObject is very similar to the code for creating a QueryTable alone. In fact, the preceding QueryTable example can be modified to use a ListObject by changing a single line of code:

Sub CreateQueryTableWithList()

Dim qryTable As QueryTable Dim rngDestination As Range Dim strConnection As String

' Define the connection string and destination range. strConnection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" &

"Data Source=C:\Files\Northwind 2007.accdb" Set rngDestination = Sheet2.Range("A1")

' Create the ListObject and get a reference to its QueryTable. Set qryTable = Sheet2.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=strConnection, Destination:=rngDestination).QueryTable

Populate the QueryTable.

qryTable.CommandText = "Customers" qryTable.CommandType = xlCmdTable qryTable.Refresh False

End Sub

The process for creating a standalone QueryTable and the process for creating a QueryTable associated with a ListObject are fundamentally the same, the only difference being that you use the arguments of the ListObjects.Add method to specify the connection string and destination. Because a ListObject can have a number of additional data sources besides the external data used in this example, you also need to specify what type of data source your connection string represents, using the SourceType argument of the ListObjects.Add method. See Chapter 6 for more details on ListObjects.

A portion of the table created by the previous code is shown in Figure 21-8.

Figure 21-8

Any time a ListObject is created from an external data source, either using VBAor through the Excel UI, an associated QueryTable object is created. You can use this QueryTable to manipulate the source data for any ListObject in a workbook.

Note that a QueryTable associated with a ListObject is not part of the Worksheet .QueryTables collection. It can only be accessed through the ListObject.QueryTable property of its associated ListObject. If you are using VBA to examine worksheets for the existence of query tables, you will need to look for them both directly in the QueryTables collection and indirectly in the ListObjects collection.

+10 -9


Post a comment