excel - 如何命名 QueryTable 使用的连接

Question

我当前的代码:

ConnectionName = "testCon"
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://test/test?values=[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]~[]&format=csv" _
    , Destination:=Range("$D$1"))
    .Name = ConnectionName
End With

这导致创建一个名为“Connection”的新连接，如果重复该过程，则创建“Connection1”，然后创建“Connection2”，依此类推。有什么方法可以命名连接吗？
.Name参数似乎是指数据范围名称，而不是连接名称。

Answer 1

最后我没有找到直接命名新查询表连接的方法，所以我不得不编写一个宏，在连接列表中查找新名称，然后重命名它。

Sub NewQueryTable(ConnectionName As String)
    OriginalConnections = ConnectionList()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://myconnection&format=csv" _
        , Destination:=Range("$D$1"))
        .Name = ConnectionName
    End With


' Looking for the newly created Connection to rename it
'''''''''''''''''''''''''''''''''''''''''''''''''''''
    UpdatedConnections = ConnectionList()
    Dim NewConnectionName As String
    Dim CheckedString As String


    For Each conn In UpdatedConnections
        CheckedString = conn
        If Not (IsInArray(CheckedString, OriginalConnections)) Then
            NewConnectionName = CheckedString
        End If
    Next conn

    With ActiveWorkbook.Connections(NewConnectionName)
            .Name = ConnectionName
            .Description = ""
    End With


End Sub


Function ConnectionList() As Variant

    Dim NumOfConnections As Integer
    Dim Counter As Integer


    NumOfConnections = 0
    Counter = 1


    Dim conn As WorkbookConnection
    For Each conn In ActiveWorkbook.Connections
        NumOfConnections = NumOfConnections + 1
    Next conn


    Dim ConnectionNames() As String, size As Integer, i As Integer
    size = NumOfConnections
    ReDim ConnectionNames(size)

    For Each conn In ActiveWorkbook.Connections
        ConnectionNames(Counter) = conn.Name
        Counter = Counter + 1
    Next conn

    ConnectionList = ConnectionNames


End Function

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

        IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)

End Function