ExcelVBA通过名称引用QueryTable对象-ExcelVBArefertoQueryTableobjectsbyname

6  

According to this MSDN link for ListObject there isn't any collection of QueryTables being a property of ListObjects. Correct code is:

根据ListObject的MSDN链接，没有任何查询表集合是ListObject的属性。正确的代码是:

Set QT = querySheet.ListObjects.items(1).QueryTable

What you possibly need is to refer to appropriate ListObject item like (just example code):

您可能需要的是引用适当的ListObject项，如(只是示例代码):

Dim LS as ListObject
Set LS = querySheet.ListObjects("My LO 1")
Set QT = LS.QueryTable

The other alternative is to refer to QT through WorkSheet property in this way:

另一种选择是通过工作表属性引用QT:

Set QT = Worksheet("QTable").QueryTables("My Query Table")

8  

In Excel 2003 and prior, an external data connection would create a QueryTable object whose parent was a worksheet. You could access the QueryTable object, for one, through the QueryTables collection object. Like most collection objects, you can pass an index number or a name to the (default) Item method to get it.

在Excel 2003和之前，外部数据连接将创建一个QueryTable对象，其父对象是工作表。例如，您可以通过QueryTables集合对象访问QueryTable对象。与大多数集合对象一样，您可以将索引号或名称传递给(默认)项方法来获取它。

Sheet1.QueryTables("MyQtName")

When you open a 2003 worksheet in a new version, it still has a QueryTable object and can be accessed the same way. Even if you convert the file format, the QueryTable persists.

当您在新版本中打开2003年的工作表时，它仍然有一个QueryTable对象，并且可以以相同的方式访问它。即使您转换了文件格式，QueryTable仍然存在。

In 2007 and later versions, there are only three ways to create a QueryTable that will be a member of Worksheet.QueryTables:

在2007年和以后的版本中，只有三种方法可以创建一个将成为Worksheet.QueryTables成员的QueryTable:

1. Through code
2. 通过代码
3. Data - From Text
4. 从文本数据,
5. Data - From Web
6. 数据——从网络

All other UI external data connections in these new versions will result not in a QueryTables member, but in a ListObject. That ListObject will have one and only one QueryTable object that can be accessed via the ListObject.QueryTable property.

这些新版本中的所有其他UI外部数据连接将不会产生一个QueryTables成员，而是产生一个ListObject。该ListObject将有且仅有一个可通过ListObject访问的QueryTable对象。QueryTable财产。

Here's the bad news. The QueryTable whose parent in a ListObject doesn't have a Name property. Well, it's there, but you will get a runtime error 1004 if you try to access it. I guess MS decided since there's only one QueryTable per ListObject, it didn't make sense that it should have a name.

这是坏消息。ListObject中父类没有Name属性的QueryTable。它在那里，但是如果你试图访问它你会得到一个运行时错误1004。我猜MS决定，因为每个ListObject只有一个QueryTable，所以它不应该有名称。

If you try to convert a Worksheet.QueryTables.QueryTable into a ListObject, the external data connection goes away and the new ListObject doesn't have a QueryTable.

如果您试图转换工作表. querytables。在ListObject中，外部数据连接消失，新的ListObject没有一个QueryTable。

Since your QueryTables.Count is returning zero, all of your QueryTables are inside ListObjects and don't have names. The ListObjects have names. You can use

因为你QueryTables。Count返回0，所有的QueryTables都在listobject中，没有名称。ListObjects名称。您可以使用

Sheet1.ListObjects("MyListName").QueryTable

Here's a function that takes a name and a worksheet and returns a QueryTable that either has that name or is a child of a ListObject that has that name.

这里有一个函数，它接受一个名称和一个工作表，并返回一个具有该名称的查询表，或者是具有该名称的ListObject的子表。

Public Function QueryTableByName(ByVal sName As String, ByRef sh As Worksheet) As QueryTable

    Dim qt As QueryTable
    Dim lo As ListObject

    On Error Resume Next
        Set qt = sh.QueryTables(sName)
    On Error GoTo 0

    If qt Is Nothing Then
        On Error Resume Next
            Set lo = sh.ListObjects(sName)
        On Error GoTo 0

        If Not lo Is Nothing Then
            On Error Resume Next
                Set qt = lo.QueryTable
            On Error GoTo 0
        End If
    End If

    Set QueryTableByName = qt

End Function