Counting Query Tables in Excel

A few years ago, Ron Coderre created his PivotPlay PLUS Add-in that you can download from my Contextures site.

This free add-in was designed for Excel 2003, and lets you view and edit the connection strings for pivot tables and query tables that are based on external data queries.

pivotplayaddin02

Testing in Excel 2010

Several people have asked about an update, so I installed the PivotPlay PLUS add-in in Excel 2010, to test it. Instead of showing up on the menu bar, the start button appears on the Excel Ribbon’s Add-Ins tab.

pivotplayaddin01

If the active worksheet has a pivot table, when you click the Ribbon button, you’ll see information about that pivot table. And, if the pivot table is based on an External data query, you can edit the connection information and the query string.

But, when I tried to change the connection information on a worksheet that had 2 query tables, the add-in didn’t work. The two blue tables are query tables, and the red one is an normal list. However, the add-in didn’t find any query tables.

pivotplayaddin03

Counting the Query Tables

The Pivot Play add-in counts the pivot tables and query tables on the active sheet. If it finds either one, the add-in opens. In Excel 2003, you could use QueryTables.Count to see if there were any tables.

Sub Count_QT_Old()
  Dim lQT As Long
  lQT = ActiveSheet.QueryTables.Count
  Debug.Print lQT
End Sub

When I used that code in Excel 2010, the count was zero, even though there were two tables based on queries.

Change the Counting Code

If we’re going to modify this add-in to work in Excel 2010, we’ll have to find a different way to check for query tables. Starting in Excel 2007, query tables changed, and now they’re part of the ListObject.

After a bit of experimentation, I found that looping through all the ListObjects, and checking their SourceType, will give a count of query tables.

Sub Count_QT_New()
  Dim lQT As Long
  Dim LO As ListObject
  For Each LO In ActiveSheet.ListObjects
    If LO.SourceType = 3 Then 'xlSrcQuery
        lQT = lQT + 1
    End If
  Next LO
  Debug.Print lQT
End Sub

When I run the revised code, it shows a count of 2 query tables, which is correct. If you know of a better way to count query tables, please let me know.

Now I’ll just have to figure out what else needs to be changed!

_______________________