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!

_______________________

You may also like...

5 Responses

  1. Tony Phillips says:

    Good evening

    Is there still a need for this as unless I have misunderstood editing was introduced in Excel 2007 via the definitions tab of the data connections property dialog box?

  2. Frans Bus says:

    Hello Debra,
    When you import a text file excel doesn’t create a listobject and sheet.querytables.count = 1.
    For all other data connections excel creates the listobject and “embeds” the querytable.
    Perhaps it’s better to focus on the workbook.connections?
    Btw, thx for your great posts.
    – Frans

    Dim cn As WorkbookConnection
    For Each cn In ThisWorkbook.Connections
    Debug.Print cn.Name, cn.Parent.Name, cn.Ranges(1).Parent.Name,
    Debug.Print Choose(cn.Type, "xlConnectionTypeOLEDB", "xlConnectionTypeODBC", _
    "xlConnectionTypeXMLMAP", "xlConnectionTypeTEXT", "xlConnectionTypeWEB")
    Next cn

  3. Aslam Labeeb says:

    Good site, very usefull tips

  4. Leo Carpentier says:

    Hi Debra,
    Thanks for the code. It works fine. Now I can start to change dynamicaly the commandtext value of my querytable :-)
    Why is important information like this nowhere found on the Microsoft site?

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *