Creating basic data reports with ListObjects and QueryTables

ListObjects

The ListObject and the collection of ListObjects was first introduced with Excel 2003 and with the release of Excel xlsx it has been improved. The ListObject represents lists of all kind and it provides end users with a set of built-in features to manipulate the ListObject and its data.

QueryTables

According to the help file to Excel a QueryTable represents: "A worksheet table built from data returned from an external data source such as an SQL Server or a Microsoft Access database."

We can either create standalone QueryTables or when we are using a ListObject create an associated QueryTable. In this article the focus is set to create an associated QueryTable.

Basic reports with ListObjects and QueryTables

The key point with the article is to show that by combining these two objects in VBA solutions we leverage the good parts from both these two objects. For obvious reasons we also find when exploring the properties and methods in depth that they overlap each other (see the second VBA code example below).

Connection strings

When using VBA and with QueryTables it requires that we use connection strings. Since there are different types of drivers we need to explicit state in the connection string what type we use. In order to use OLE DB Drivers we need to add the prefix "OLEDB;" to the connection string in use:

# When using Microsoft Jet 4.0 OLE DB Provider to access an Access 2003 database or earlier the following example shows the connection string:  
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb;Persist Security Info=False"  
# When using Microsoft Access 12.0 OLE DB Provider to access Access accdb databases the connection string should look like:  
OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Northwind.accdb;Persist Security Info=False"  
# For connecting to SQL Server 2005 databases one of the following drivers can be used:  
"OLEDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=DENNIS\SQLEXPRESS"  
"OLEDB;Provider=SQLOLEDB;Data Source=DENNIS\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Northwind"

The case

In this case I use the SQL Server 2005 Express Edition’s Northwind database. If you don’t want to or cannot use this database it can be replaced with the Northwind database shipped with Microsoft Access. If so then you can use one of the mentioned connection strings above.

What the VBA procedure does is the following:

• Create the ListObject
• Populate some importants properties of it and create the associated QueryTable.
• Populate the necessary properties of the QueryTable.
• Execute and retrieve the data from the database and add the data to the first worksheet in the active workbook.

Although this case uses a simple SQL query we can use parameterized queries where the Parameters property of the QueryTable can be used.

Option Explicit  
Sub Create_Report_Based_On_ListObject_QueryTable()  
'The connection string.  
Const stCon As String = "OLEDB;Provider=SQLOLEDB;Data  
Source=DENNIS\SQLEXPRESS;"& _  
"Integrated Security=SSPI;Initial Catalog=Northwind"  
'The SQL Expression.  
Const stSQL As String = "SELECT ProductName AS Name, " & _  
"CategoryID AS Category, " & _  
"QuantityPerUnit AS [Quantity per Unit], " & _  
"[UnitPrice] AS [Price per Unit] " & _  
"From Products ORDER BY CategoryID;"  
'Excel variables.  
Dim wbBook As Workbook  
Dim wsSheet As Worksheet  
Dim rnStart As Range  
Dim qtTable As QueryTable  
Set wbBook = ActiveWorkbook  
Set wsSheet = wbBook.Worksheets(1)  
Set rnStart = wsSheet.Range("A1")  
'The creation of the ListObject and the associated QueryTable.  
Set qtTable = wsSheet.ListObjects.Add( _  
SourceType:=xlSrcQuery, _  
Source:=stCon, _  
Destination:=rnStart).QueryTable  
'Populate some major properties of the QueryTable.  
With qtTable  
.CommandText = stSQL  
.CommandType = xlCmdSql  
'In order to see the output for the first time  
'we need to use the Refresh command.  
.Refresh  
.RefreshOnFileOpen = True  
End With  
End Sub

What we have created

In order to understand what we have achieved with the above VBA procedure we can print it out in the Immediate Window the values that different properties of the different objects contain. What should be notable is that in Excel 2007 we have got a new object and collection by the name of "WorkbookConnection ". This object looks promising but it’s only in its early stage so the future will give us additional guidelines about it.

Sub See_What_We_Have_Created()  
Dim wbBook As Workbook  
Dim wsSheet As Worksheet  
Dim loObject As ListObject  
Dim qtTable As QueryTable  
Set wbBook = ActiveWorkbook  
'For Excel xlsx only.  
If Val(Application.Version) = 12 Then  
Dim wbConnection As WorkbookConnection  
Set wbConnection = wbBook.Connections(1)  
With wbConnection.OLEDBConnection  
Debug.Print "************** Workbook Connection *************"  
Debug.Print .Connection  
Debug.Print .CommandText  
Debug.Print .CommandType  
Debug.Print  
End With  
End If  
Set wsSheet = wbBook.Worksheets(1)  
Set loObject = wsSheet.ListObjects(1)  
Set qtTable = loObject.QueryTable  
'Some information about the created ListObject's properties.  
With loObject  
Debug.Print "************** Created ListObject *************"  
Debug.Print .DataBodyRange.Address  
Debug.Print .Range.Address  
Debug.Print .ListColumns.Count  
Debug.Print .ListRows.Count  
Debug.Print .HeaderRowRange.Address  
Debug.Print .Range.Address  
Debug.Print  
End With  
'Some information about the associated QueryTable's properties.  
With qtTable  
Debug.Print "************** Created QueryTable *************"  
Debug.Print .CommandText  
Debug.Print .CommandType  
Debug.Print .Connection  
End With  
End Sub

Conclusion

By combining the ListObject and the QueryTable object we can leverage the real power in each of these objects when creating basic data reports. The above VBA solution should give an idea about the framework and what is required to create basic reports.