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

작성자
dra****
작성일
2021-09-23 13:54
조회
191

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

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

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

1.1 Query 객체 생성

Workbook.Queries.Add (Name, Formula, Description)

 

1.2. Connection 객체 생성

Workbook.Connections.Add2 (Name, Description, ConnectionString, CommandText, lCmdtype, CreateModelConnection, ImportRelationships)

 

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

ActiveSheet.ListObjects.Add(SourceType, Source, Destination)

 

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 메서드를 호출 비로서 서버에 실제 조회하고 그 내용을 받아와 엑셀의 지정한 셀에 그 내용이 뿌려집니다. 참고로 .RefreshListObject에서 해도 되고, 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

 

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

회원등급 : 나무2단계
포인트 : 2854 EP
전체 7

  • 2021-09-26 17:38

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

    혹시 이 내용도 알 수 있을까요? 올려주시는 고급 정보로 너무 잘 공부하고 있습니다


  • 2021-09-24 20:07

    역시 전문가십니다. 잘 배웠습니다


    • 2021-09-24 20:21

      아마도 대부분 조금 읽다가 포기하실 듯 합니다. 이거 관계 연구만 1주일 넘게 했습니다. ㅠㅠ

      동적 생성 단계만 극복하면 동적 갱신부터는 누구나 쉽게 하는 내용입니다. 무지하게 쉬워요.

      동적으로 쿼리복제해서 이름만 바꾸고 Formula 수정하고, 표 지정해서 손 쉽게 가는 방법도 있습니다. 전제가 M Script 를 조금만 이해 할 수 있다면.


      • 2021-09-24 22:25

        M 스크립트는 사용해보니 SQL+엑셀+자바를 섞은 느낌이더라구요..ㅎㅎ

        파워쿼리 쓰다보면 자연스럽게 보게되는지라.. 기본적인 구문은 아는데 이걸 외워서쓰자니 너무 어려운지라 검색해서 사용했는데 다행히 레퍼런스가 많아서 구현하는건 어렵지 않았던걸로 기억합니다

        다음에는 올려주신거 참고해서 VBA로도 만들어봐야겠네요..ㅎㅎ


  • 2021-09-29 18:29

    좋은 정보 감사합니다.


  • 2021-09-28 11:58

    좋은 정보 감사합니다.


전체 43
번호 제목 작성자 작성일 추천 조회
40
[엑셀 VBA] 파워쿼리 자동화 4. CRUD (2)
dra**** | 2021.10.12 | 추천 0 | 조회 57
dra**** 2021.10.12 0 57
39
[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class (6)
dra**** | 2021.10.07 | 추천 0 | 조회 89
dra**** 2021.10.07 0 89
38
[엑셀 VBA] 파워쿼리 자동화 2. 자동화 실습 (3)
dra**** | 2021.09.28 | 추천 0 | 조회 130
dra**** 2021.09.28 0 130
37
[엑셀 VBA] 개체 Reference - QueryTable Object 첨부파일
dra**** | 2021.09.26 | 추천 0 | 조회 55
dra**** 2021.09.26 0 55
36
[엑셀 VBA] 파워쿼리 자동화 1. 자동화 순서 (7)
dra**** | 2021.09.23 | 추천 0 | 조회 191
dra**** 2021.09.23 0 191
35
[엑셀 VBA] 개체 Reference - ADODB.Recordset : Database 필수 첨부파일 (3)
dra**** | 2021.09.16 | 추천 1 | 조회 156
dra**** 2021.09.16 1 156
34
[잡설] IT가 흘러가고 있는 방향에 대한 단상 - 무엇을 공부해야 하는가? (13)
dra**** | 2021.09.16 | 추천 2 | 조회 132
dra**** 2021.09.16 2 132
33
[엑셀 VBA] MySQL 02. Range -> 배열 -> INSERT (4)
dra**** | 2021.09.16 | 추천 0 | 조회 98
dra**** 2021.09.16 0 98
32
[엑셀 VBA] MySQL 01. 워크시트 Custom Properties 활용하기 (3)
dra**** | 2021.09.14 | 추천 1 | 조회 78
dra**** 2021.09.14 1 78
31
[엑셀 VBA] 개체 Reference - ListObject Object - [표]가지고 놀기 첨부파일 (10)
dra**** | 2021.08.29 | 추천 4 | 조회 151
dra**** 2021.08.29 4 151
30
[엑셀 VBA] Windows API - winuser.h : 윈도우즈 프로그램의 핵심 첨부파일 (8)
dra**** | 2021.08.17 | 추천 3 | 조회 149
dra**** 2021.08.17 3 149
29
[잡설] 엑셀이 웹으로 가야 하는 이유 (18)
dra**** | 2021.08.16 | 추천 10 | 조회 321
dra**** 2021.08.16 10 321
28
[엑셀 VBA] 개체 Reference - FileSystem Object 첨부파일 (3)
dra**** | 2021.08.15 | 추천 1 | 조회 107
dra**** 2021.08.15 1 107
27
[엑셀 VBA] 데이터 자동화 - 웹에서 제공하는 MySQL Sever를 이용한 자동화 개념 (13)
dra**** | 2021.08.05 | 추천 1 | 조회 272
dra**** 2021.08.05 1 272
26
[엑셀 VBA] 개체 Reference - APPLICATION Object 첨부파일 (1)
dra**** | 2021.08.05 | 추천 0 | 조회 97
dra**** 2021.08.05 0 97
25
[엑셀 VBA] 개체 Reference - WORKBOOKS, WORKBOOK Object 첨부파일 (4)
dra**** | 2021.07.25 | 추천 2 | 조회 143
dra**** 2021.07.25 2 143
24
[엑셀 VBA] 개체 Reference - WORKSHEETS, WORKSHEET Object 첨부파일 (5)
dra**** | 2021.07.11 | 추천 1 | 조회 174
dra**** 2021.07.11 1 174
23
[엑셀 VBA] 개체 Reference - RANGE Object 첨부파일 (4)
dra**** | 2021.07.06 | 추천 3 | 조회 174
dra**** 2021.07.06 3 174
22
[엑셀 VBA] 자동화 - 엑셀 데이터베이스의 기본 작동 원리 및 구현 방법 (16)
dra**** | 2021.06.28 | 추천 4 | 조회 558
dra**** 2021.06.28 4 558
21
지배하는 자 Vs. 지배당하는 자 (16)
dra**** | 2021.06.27 | 추천 13 | 조회 361
dra**** 2021.06.27 13 361