Creating basic data reports with ListObjects and QueryTables
In this article we will take a closer look on how we can create a basic data report with VBA for Excel 2003 and 2007. With a basic report I refer to solutions where data is, for instance, retrieved from a central database, which is then placed in a worksheet.
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.
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).
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"
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
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.