Using ADO with ListObject's QueryTable

Post date: Feb 9, 2013 4:35:28 PM

There are several ways to transfer data from databases to Excel. The method discussed here:

• • is fast

  • robust and feature rich

  • can be refreshed to the source database

  • can be coded in just one line

  • uses any recordset produced by any method including ADO

  • Leverages XL's tables and its features

Competing methods are:

• • Move fields to cells one at a time; Ex Cell(1,1) = ADORecorset.Fields(1)¹ Slowest method. Can't be "refreshed".

  • GetRows² Faster. Uses arrays. Most error prone (in my experience). Can't be "refreshed".

  • CopyFromRecordSet³ Fast and reliable. My "Go to" method. Can't be "refreshed".

  • QueryTable using Get External Data menu option⁴ Doesn't require VBA. Produced by MS Query. Supports "refresh".

NOTE! This is different from the QueryTable created using MS Query. The QueryTable in MS Query can be altered by changing its CommandText parameter. But if MS Query hasn't created a QueryTable we can create one without MS Query like so:

expression.ListObjects.Add(SourceType, Source, LinkSource, _

HasHeaders, Destination, TableStyleName)⁵

Where expression evaluates to a worksheet object like ActiveSheet.

Coding Example #1 Creating New QueryTable:

Dim oCN As Object 'Connection

Dim oRS As Object 'Recordset

Set oCN = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")

oCN.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=C:\Northwind 2003.accdb;"

ORS.Open "Select * From [Employees 2003]", oCN

ActiveSheet.ListObjects.Add(xlSrcQuery, oRS, _

Destination:=Selection).QueryTable.Refresh

oRS.Close

oCN.Close

Set oRS = Nothing

Set oCN = Nothing

Coding Example #2 Updating Existing QueryTable (assumes first table on active worksheet):

Dim oCN As Object 'Connection

Dim oRS As Object 'Recordset

Set oCN = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")

oCN.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=C:\Northwind 2003.accdb;"

ORS.Open "Select * From [Employees 2003]", oCN

With ActiveSheet.ListObject(1).QueryTable

Set .Recordset = oRS

.Refresh

End With

oRS.Close

oCN.Close

Set oRS = Nothing

Set oCN = Nothing

Notes!

• • Error checking removed for illustration purposes only.

  • I normally keep connections open, reuse them for speed and close them when the app closes.

References

• 1. Field at a time: http://msdn.microsoft.com/en-us/library/office/ff197799.aspx

  2. GetRows http://msdn.microsoft.com/en-us/library/windows/desktop/ms677500(v=vs.85).aspx

  3. CopyFromRecordset http://msdn.microsoft.com/en-us/library/office/aa223845(v=office.11).aspx

  4. QueryTable: http://msdn.microsoft.com/en-us/library/office/aa174289(v=office.11).aspx

     1. Using MS Query: http://www.youtube.com/watch?v=I1qvqMiGyhc

     2. Parameterized QueryTable: http://www.youtube.com/watch?v=P9cUYpXIKsU

  5. ListObjects.Add: http://msdn.microsoft.com/en-us/library/office/bb211863(v=office.12).aspx