[엑셀 VBA] 파워쿼리 자동화 1. 자동화 순서

MySQL이든, MS-SQL이든, MongoDB든 데이터 베이스 안에 TABLE을 하나 생성하고 데이터를 집어 넣었다는 가정하에 포스팅 하려 합니다.

참고) CRUD : CREATE, READ, UPDATE, DELETE

1. 동적으로 파워쿼리를 생성하는 순서

1.1 Query 객체 생성

1.2. Connection 객체 생성

1.3 ListObject(표) 객체 생성하여 엑셀 시트로 조회한 내용을 가져오기

1.4 ListObject.QueryTable을 이용한 추가 옵션 집어 넣기

2. 각각의 과정에 등장하는 객체 이해하기

파워쿼리로 자료를 가져오려면 3가지가 필요 입니다.

Query: Workbook의 Queries 컬렉션을 통해 추가하거나 접근 할 수 있습니다. Query.Name은 일관성 있게 짓는게 중요합니다.

Connection: Workbook의 Connections 컬렉션을 통해 추가하거나 접근 할 수 있습니다. Connection.Name도 위의 Query.Name과 같게 하거나 일관성 있게 짓는게 중요합니다.

ListObject: WorkSheet의 ListObjects 컬렉션을 통해 추가하거나 접근 할 수 있습니다. 이 객체도 이름 일관성 있게 지어야 겠지요.

QueryTable: listObject.QueryTable이라 하면 QueryTable 객체가 반환되어 들어 옵니다. 이를 통해 각각의 속성에 접근할 수 있습니다. 파워쿼리를 수동으로 해 본 적이 있다면 각각의 옵션 주는 것이 이 객체를 통해 동적으로 할 수 있습니다.

cf) 개인적으로 qryEMP, connEMP, lobjEMP, qtableEMP 이렇게 명명합니다. 수십개의 파워쿼리들도 동적으로 접근해서 객체를 참조하게 하려면 "qry" & Suffix, 이런식으로 코딩하면 이 부분들도 모두 동적으로 접근할 수 있습니다.

3. 연결해서 조회하는 과정

Query를 통해서 Database에 조회할 내용을 Connection개체에 인수로 전달하면 그 내용을 담습니다. 아직 화면에 뿌려진 상태가 아닙니다. ListObject에 Connection를 인수로 넘겨 주면 이제 세팅이 끝난 것입니다. 이제 .Refresh 메서드를 호출 비로서 서버에 실제 조회하고 그 내용을 받아와 엑셀의 지정한 셀에 그 내용이 뿌려집니다. 참고로 .Refresh는 ListObject에서 해도 되고, QueryTable(부모가 ListObject이기에)에서 해도 됩니다.

4. 객체 생성의 매개 변수

4.1 Set WorkbookQuery= Workbook.Queries.Add (Name, Formula, Description)

.Queris.Add를 하면 WorkbookQuery라는 객체에 정보를 담아서 반환합니다. 저기서 WorkbookQuery는 변수명이 아니라 그 객체가 반환된다는 의미로 쓴 것입니다.

    .Name : WorkbookQuery의 이름입니다. 네이밍을 잘해서 지으면 됩니다. 이 이름을 이용해서 참조하게 됩니다.

    .Formula: M 스크립트 언어만 가능하고 대입합니다. 겁 먹으실 필요 없습니다. SQL만 집어 넣을 공간이니까요.

    .Description: 이 의 설명을 간단히 적는 곳입니다. 기입해 두면 협업이나 본인이 나중에 이게 뭐지 할 때 필요할 겁니다.

4.2 Set WorkbookConnection= Workbook.Connections.Add2 (Name, Description, ConnectionString, ... )

.Connections.Add2를 하면 WorkbookConnection라는 객체에 정보를 담아서 반환합니다. 저기서 WorkbookConnection는 변수명이 아니라 그 객체가 반환된다는 의미로 쓴 것입니다.

    .ConnectionString = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & qryName & ";Extended Properties="

여기서 qryName은 위에 언급한 .Name 입니다.

나머지 속성들은 제가 이전에 올린 Workbook reference를 참고 하시거나 VBA 사이트를 참고하시면 됩니다.

4.3 Set ListObject = ActiveSheet.ListObjects.Add (SourceType, Source, Destination)

.ListObjects.Add 를 하면 ListObject라는 객체에 정보를 담아서 반환합니다. 저기서 ListObject는 변수명이 아니라 그 객체가 반환된다는 의미로 쓴 것입니다.

    .SourceType = 4

    .Source = ActiveWorkbook.Connections(connName)

    .Destination = Range(pasteAddress)

5. 워크북이나 워크시트에 객체가 있는가 확인해보는 간단 코드

Sub listOfQueries()
   Dim wbQry As WorkbookQuery
 
   For Each wbQry In ThisWorkbook.Queries
      Debug.Print wbQry.NAME
   Next
End Sub

Sub listOfWorkbookConnections()
   Dim wbConn As WorkbookConnection
   For Each wbConn In ThisWorkbook.Connections
      Debug.Print wbConn.NAME
   Next
End Sub

Sub listofListObjects()
   Dim lobj As ListObject
   For Each lobj In Activesheet.ListObjects
      Debug.Print lobj.NAME
   Next
End Sub

6. 새로운 파워쿼리 동적 생성시 주의사항

같은 이름을 가진 객체가 있으면 에러가 생기니 반드시 체크하는 코드를 만들고, 없으면 그냥 생성하고 있으면 그 속성을 원하는 방향으로 바꾸는 방법으로 코딩을 해야 합니다. 다음은 실제 사용고 있는 코드의 일부 입니다.

다음 포스팅에 소개할, 동적으로 조회할 때 strFormula 부분만 수정하고 Refresh를 하면 자동으로 조회가 됩니다.

   If IsWorkbookQueryExist(qryName) = False Then
      ActiveWorkbook.Queries.Add _
         NAME:=qryName, _
         formula:=strFormula, _
         Description:="WorkbookQuery for " & suffix
   End If

다음에 포스팅 할 내용은, 실제 파워쿼리 동적 생성 코드와 동적 조회 코드를 분석하는 내용입니다.