excel 查询 表关联_在Excel中计算查询表

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.

几年前，Ron Coderre创建了他的PivotPlay PLUS加载项，您可以从我的Contextures网站下载该加载项。 这个免费的加载项是为Excel 2003设计的，使您可以查看和编辑基于外部数据查询的数据透视表和查询表的连接字符串。

在Excel 2010中测试 (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.

有几个人询问更新，因此我在Excel 2010中安装了PivotPlay PLUS加载项以对其进行测试。 开始按钮没有显示在菜单栏上，而是显示在Excel功能区的“加载项”选项卡上。

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.

但是，当我尝试在具有2个查询表的工作表上更改连接信息时，该加载项不起作用。 蓝色的两个表是查询表，红色的是普通列表。 但是，该加载项未找到任何查询表。

查询表计数 (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.

Pivot Play加载项对活动工作表上的数据透视表和查询表进行计数。 如果找到任何一个，则外接程序将打开。 在Excel 2003中，可以使用QueryTables.Count来查看是否有任何表。

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.

当我在Excel 2010中使用该代码时，即使有两个基于查询的表，计数也为零。

更改计数码 (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.

如果要修改此加载项以使其在Excel 2010中工作，则必须找到其他方法来检查查询表。 从Excel 2007开始，查询表已更改，现在它们已成为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.

经过一些试验，我发现循环遍历所有ListObject，并检查它们的SourceType，将得出查询表的数量。

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.

当我运行修改后的代码时，它显示了2个查询表的数量，这是正确的。 如果您知道一种更好的查询表计数方法，请告诉我。

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

现在，我只需要弄清楚还有哪些需要更改！

翻译自: https://contexturesblog.com/archives/2013/05/02/counting-query-tables-in-excel/

excel 查询 表关联