excel - 在 VBA 中删除不需要的查询

Question

如何从工作簿中删除所有不需要的查询？

Sub DeleteQuery()
    Dim queries As Variant
    queries = Array("q1", "q2", "q3")
    For Each qr In ThisWorkbook.queries
        'Not sure about the syntax of the following line
        If qr not in queries Then
        qr.Delete
    Next qr
End Sub

如果查询不在列表中，则应将其删除

ActiveWorkbook.Queries("Query1").Delete

不会工作，因为不需要的查询的名称不清楚

Answer 1

您需要遍历查询，然后遍历整个数组以找到匹配项，如果不存在匹配项，则删除。

Sub testingPQ()

    Dim vQuery As Variant
    Dim arrQueries() As Variant
    Dim i As Long

    arrQueries = Array("q1", "q2", "q3")


    For Each vQuery In ThisWorkbook.Queries
        'loop through array to check for each query
        For i = LBound(arrQueries) To UBound(arrQueries)
            If vQuery.Name = arrQueries(i) Then
                'do not delete
                Exit For
            End If
            If i = UBound(arrQueries) Then
                'delete - no match
                vQuery.Delete
            End If
        Next i
    Next vQuery

End Sub