이름별 쿼리 테이블 개체 참조

이름별 쿼리 테이블 개체 참조

저는 QueryTables를 사용하는 VBA와 함께 MS Excel 2013 툴을 개발하고 있습니다.

한 가지 불편한 점은 Excel 워크시트 내의 기존 쿼리 테이블에 액세스하는 것입니다.현재 쿼리 테이블에 액세스할 수 있는 유일한 방법은 정수 인덱싱뿐입니다.빠른 개념 증명을 위해 다음 코드를 고안했습니다.

Sub RefreshDataQuery()

Dim querySheet As Worksheet
Dim interface As Worksheet

Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")

Dim sh As Worksheet
Dim QT As QueryTable

Dim startTime As Double
Dim endTime As Double

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

startTime = Timer
QT.Refresh
endTime = Timer - startTime

interface.Cells(1, 1).Value = "Elapsed time to run query"
interface.Cells(1, 2).Value = endTime
interface.Cells(1, 3).Value = "Seconds"

End Sub

이것은 효과가 있지만, 저는 이런 식으로 하고 싶지 않습니다.최종 제품 도구에는 최대 5개의 쿼리 테이블이 있습니다.쿼리 테이블의 이름을 참조하고 싶습니다.

번역 방법:

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

다음 항목에 대해 설명합니다.

Set QT = querySheet.ListObjects.items.QueryTable("My Query Table")

Excel 2003 이전 버전에서는 외부 데이터 연결을 통해 상위가 워크시트인 QueryTable 개체가 생성되었습니다.QueryTable 컬렉션 개체를 통해 QueryTable 개체에 액세스할 수 있습니다.대부분의 컬렉션 개체와 마찬가지로 인덱스 번호나 이름을 (기본값) Item 메서드에 전달하여 가져올 수 있습니다.

Sheet1.QueryTables("MyQtName")

새 버전으로 2003 워크시트를 열어도 쿼리 테이블 개체가 있으며 동일한 방식으로 액세스할 수 있습니다.파일 형식을 변환해도 쿼리 테이블은 유지됩니다.

2007년 이후 버전에서는 워크시트의 멤버가 될 쿼리 테이블을 만드는 방법이 세 가지뿐입니다.쿼리 테이블:

1. 스루코드
2. 데이터 - 텍스트에서
3. 데이터 - 웹에서

이 새 버전의 다른 모든 UI 외부 데이터 연결은 QueryTables 멤버가 아니라 ListObject가 됩니다.이 ListObject에는 ListObject를 통해 액세스할 수 있는 QueryTable 개체가 하나뿐입니다.QueryTable 속성입니다.

안 좋은 소식이 있습니다.ListObject의 상위 항목에 Name 속성이 없는 QueryTable입니다.글쎄, 거기에 있지만 접속을 시도하면 런타임 에러 1004가 발생합니다.MS는 ListObject당 쿼리 테이블이 하나밖에 없어서 이름을 붙여야 한다는 게 말이 안 된다고 판단한 것 같습니다.

워크시트를 변환하려는 경우.테이블 쿼리.QueryTable in a ListObject, 외부 데이터 연결이 사라지고 새 ListObject에 QueryTable이 없습니다.

쿼리 테이블 이후로.카운트가 0을 반환하고 있습니다. 모든 쿼리 테이블이 ListObjects 내부에 있으며 이름이 없습니다.ListObjects에 이름이 있습니다.사용가능

Sheet1.ListObjects("MyListName").QueryTable

이름과 워크시트를 사용하여 해당 이름을 가지거나 해당 이름을 가진 ListObject의 자식인 QueryTable을 반환하는 함수가 있습니다.

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

ListObject에 대한 이 MSDN 링크에 따르면 다음의 컬렉션이 없습니다.QueryTables의 소유물인ListObjects. 정확한 코드는:

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

당신이 필요로 하는 것은 적절한 것을 참조하는 것입니다.ListObject item예(예시 코드):

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

다른 대안은 QT ~ 를 참조하는 것입니다.WorkSheet property다음과 같은 방식으로

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

작동 내용:

Range("ClassifiedAsSupply").ListObject.QueryTable.Refresh BackgroundQuery:=True

Dingbat data : https://www.dingbatdata.com/2017/11/24/referencing-listobjects-with-vba/ # comment-719

언급URL : https://stackoverflow.com/questions/18067070/refer-to-querytable-objects-by-name