[엑셀 VBA] 파워쿼리 자동화 2. 자동화 실습

작성자
dra****
작성일
2021-09-28 12:05
조회
225

이 전에는 쿼리 동적 생성의 개념에 대해서 간략히 포스팅 해보았습니다. 그 과정을 간단히 살펴보면

  1. WorkbookQuery 생성 ▶ Formula 속성에 M Script 표현식 대입
  2. WorkbookConnection 생성
  3. ListObject 생성
  4.  (Option) ListObject.QueryTable 설정

 

1. WorkbookQuery 생성 : M SCript 언어에 MySQL 설정 및 SQL 대입하기

1.1 기본 문법

let
    Source = MySQL.Database(server, database, [ReturnSingleDatabase=true, Query="SELECT * FROM tableTest"]), Transformed = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"AMOUNT", Currency.Type}})
in 
    Transformed

 

1.2 server, database, query를 동적으로 생성하는 변수를 반들어 할당하기

formula:="let Source = MySQL.Database(" & serverName & ", " & dbName & ", [ReturnSingleDatabase=true, Query=" & strSQL & "]), Transforemd = Table.TransformColumnTypes(Source,{{""DATE"", type date}, {""AMOUNT"", Currency.Type}}) in Transformed"

 

각각의 변수를 주입하면 이제 서버명, DB명, SQL이 상황이 바뀔때마다 동적으로 만들면 알아서 자동 갱신이됩니다.

 

1.3 실전 코드

   ActiveWorkbook.Queries.Add _
      NAME:=qryName, _
      formula:="let Source = MySQL.Database(" & serverName & ", " & dbName & ", [ReturnSingleDatabase=true, Query=" & strSQL & "]), Transformed = Table.TransformColumnTypes(Source,{{""DATE_CA"", type date}, {""AMOUNT"", Currency.Type}}) in Transformed", _
      Description:="Cash Advance Query Table"

 

여기서 qryName, serverName, dbName 등등은 변수로 할당 받아 넘겨 주는 것입니다.

이렇게 하면  qryName으로 지정한 WorkbookQuery가 생성이 됩니다. 이를 WorkbookConnection에 넘겨 주어야 합니다.

 

2. WorkbookConnection 생성

Workbooks("YS_MANAGE.xlsm").Connections.Add2 _
    NAME:=connName, _
    Description:="Connection To  '" & qryName & "'.", _
      ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="& qryName & ";Extended Properties=", _
      CommandText:="""qryCA""", _
      lCmdtype:=6, _
      CreateModelConnection:=True, _
      ImportRelationships:=False

 

connNameListObject에 넘겨주기 위한 변수입니다. ConnectionString에서 qryName으로 위에 생성한 WorkbookQuery를 전달해 줍니다. CommandText 에 있는 qryCA는 제가 실제 qryName="qryCA"로 할당해 놓은 부분입니다. 일부러 저 부분은 변수가 아닌 할당한 String으로 남겨두었습니다.

 

3. ListObject 생성

   With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook.Connections(connName), Destination:=Range(pastAddress)).QueryTable
      .RowNumbers = False
      .PreserveFormatting = True
      .RefreshStyle = 1                   '1 = xlInsertDeleteCells, 2 = xlInsertEntireRows, 0 = xlOverwriteCells
      .AdjustColumnWidth = True
      .ListObject.DisplayName = qryName
      .Refresh
   End With

 

ActiveSheet ... Source:=ActiveWorkbook.Connections(connName), Destination:=Range(pastAddress)) ...

여기서 connName으로 WorkbookConnection을 넘겨주고, Destination에 지정함 범위로 표를 출력합니다.

ActiveSheet.... ).QueryTable을 통해 ListObject를 QueryTable 객체로 변환하여 추가 옵션을 지정합니다.

.Refresh를 통해 서버에서 데이터를 가지고와서 시트에 뿌려 줍니다.

 

아래와 같은 결과가 나오며 이제 조건에 따른 갱신, 수정, 삭제, 추가 하는 부분만 코딩하면 끝입니다.

 

글로 이 모든 과정을 표현하기가 상당이 어렵습니다.

파워쿼리 자동화를 다루는 유튜버과 관련 참조 자료가 없어서 이거 연구하는데 3개월 정도 걸린 듯 합니다.

이것을 이해하고 난뒤 지금은 모든 코딩이 파워쿼리로 바뀌었습니다. 시트에 자료 입력은 좋은 생각이 아닌 듯 합니다. DB Server로 자료 입력하고 엑셀에서는 조회 가공만 하는 것이 좋다는 것을 깨닫느데 꽤 오랜 시간이 걸린 듯 합니다.

지금은 생성만 수동으로 하고, 생성된 workbookquery만 복제하여 formular만 바꿔서 자동화 하는 쪽으로 진행합니다. 훨씬 빠르고 수월하게 진행이 됩니다. 위의 기초가 있어 가능하게 된 것입니다.

다음 포스팅에서는 ListObject 핸들링과 데이터 조건 조회, 수정, 생성, 삭제에 관한 내용이며, 제가 만든 MySQL 클래스를 공개하겠습니다.

전체 8

  • 2021-10-05 03:38

    구조는 간단해보이지만, 첨부해주신 사진의 파일 정도의 툴을 만들려면.. (로우DB에 따라 다르겠지만..ㅎㅎ;;) M 함수에 대한 이해도가 상당히 많이 필요하지 않을까 생각됩니다. (dra**님께는 흥~ 풀어서 펭~ 하면 나오는 수준이겠지만요 ㅜㅜ)

    이게 DAX랑 크게 다르지 않기 때문에 엑셀 -> DAX -> M 함수로 넘어가면 난이도는 높지 않을것 같지만.. 예전에 살펴보니 이게 비스무리한 함수가 복잡하게 꼬여있어서..ㅡㅡ; (텍스트, 데이트 등..) 레퍼런스를 옆에 끼고 쓰게되더라구요 ㅎㅎ

    실제 툴을 구현하려면 추가로 상당한 공부가 필요하겠지만, 쿼리를 VBA로 구축하는 베이직 플로우에 대해서 다시 한 번 정리할 수 있었습니다 ㅎ 좋은 자료 감사드려요


    • 2021-10-05 12:17

      @더블유에이 님 처음 구현하고자 마음 먹었을 때, 어떻게 해야 할 지 몰라 맨땅에 해당 했습니다.

      특히 웹 공부하고 다시 돌아오니 아무것도 기억이 나지 않아 애 먹었습니다.

      다행히 제가 만들어 놓은 reference들 덕에 기억을 되살려서 ...

      reference들이 그사이 많이 update되었습니다. 이 말은 저도 reference 끼고 살았다는...

      M Script는 DB 정보와 SQL 전달자로서의 역할만 합니다.
      SQL로 다 해결합니다. transform은 거의 사용 안 합니다.
      테이블을 모두 관계형으로 디자인해서 JOIN을 제일 많이 쓰네요.
      합계같은것도 SUM하고 GROUP BY로 주로 하고

      그리고 표에 예전에 몰랐던 너무 막강한 기능들이 있어 그걸 또 많이 사용하네요.


      • 2021-10-05 16:56

        @dra**** 님 항상 노력하시는 모습이 정말 부럽습니다..ㅎ 저도 앞으로도 많이 배워야겠네요 ㅜㅜ

        감사합니다


  • 2021-10-28 14:26

    감사합니다.


  • 2021-11-12 12:14

    감사 합니다.~

     


  • 2021-11-27 01:51

    감사합니다


  • 2021-11-14 15:15

    감사합니다


  • 2021-10-27 17:15

    감사합니다


전체 48
번호 제목 작성자 작성일 추천 조회
23999
New [엑셀 VBA] Snippet - Public IP, Local IP, Mac Address 구하기 (4)
dra**** | 2021.11.25 | 추천 1 | 조회 33
dra**** 2021.11.25 1 33
23794
[엑셀 VBA] Snippet - 변수를 clipboard로 복사하기 (5)
dra**** | 2021.11.21 | 추천 2 | 조회 54
dra**** 2021.11.21 2 54
23337
HTML 라이브러리 사용하기 귀찮을 때, 사용하는 파싱함수 (getElementsByTag, getAttribute) (6)
트로피 오빠두엑셀 | 2021.11.09 | 추천 0 | 조회 70
트로피 오빠두엑셀 2021.11.09 0 70
23142
[잡설] 엑셀을 이용한 웹 자동화: SEO 이야기 (10)
dra**** | 2021.11.04 | 추천 0 | 조회 154
dra**** 2021.11.04 0 154
22802
[잡설] 웹 서버에서 내보내는 엑셀 파일 및 데이터 이야기 (6)
dra**** | 2021.10.25 | 추천 0 | 조회 111
dra**** 2021.10.25 0 111
22325
[엑셀 VBA] 파워쿼리 자동화 4. CRUD (3)
dra**** | 2021.10.12 | 추천 1 | 조회 150
dra**** 2021.10.12 1 150
22161
[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class (8)
dra**** | 2021.10.07 | 추천 0 | 조회 170
dra**** 2021.10.07 0 170
21839
[엑셀 VBA] 파워쿼리 자동화 2. 자동화 실습 (8)
dra**** | 2021.09.28 | 추천 0 | 조회 225
dra**** 2021.09.28 0 225
21764
[엑셀 VBA] 개체 Reference - QueryTable Object 첨부파일 (2)
dra**** | 2021.09.26 | 추천 0 | 조회 95
dra**** 2021.09.26 0 95
21675
[엑셀 VBA] 파워쿼리 자동화 1. 자동화 순서 (8)
dra**** | 2021.09.23 | 추천 0 | 조회 393
dra**** 2021.09.23 0 393
21496
[엑셀 VBA] 개체 Reference - ADODB.Recordset : Database 필수 첨부파일 (4)
dra**** | 2021.09.16 | 추천 1 | 조회 190
dra**** 2021.09.16 1 190
21491
[잡설] IT가 흘러가고 있는 방향에 대한 단상 - 무엇을 공부해야 하는가? (21)
dra**** | 2021.09.16 | 추천 4 | 조회 218
dra**** 2021.09.16 4 218
21482
[엑셀 VBA] MySQL 02. Range -> 배열 -> INSERT (5)
dra**** | 2021.09.16 | 추천 0 | 조회 202
dra**** 2021.09.16 0 202
21412
[엑셀 VBA] MySQL 01. 워크시트 Custom Properties 활용하기 (4)
dra**** | 2021.09.14 | 추천 1 | 조회 112
dra**** 2021.09.14 1 112
20740
[엑셀 VBA] 개체 Reference - ListObject Object - [표]가지고 놀기 첨부파일 (12)
dra**** | 2021.08.29 | 추천 4 | 조회 218
dra**** 2021.08.29 4 218
20211
[엑셀 VBA] Windows API - winuser.h : 윈도우즈 프로그램의 핵심 첨부파일 (8)
dra**** | 2021.08.17 | 추천 3 | 조회 202
dra**** 2021.08.17 3 202
20194
[잡설] 엑셀이 웹으로 가야 하는 이유 (23)
dra**** | 2021.08.16 | 추천 12 | 조회 476
dra**** 2021.08.16 12 476
20153
[엑셀 VBA] 개체 Reference - FileSystem Object 첨부파일 (3)
dra**** | 2021.08.15 | 추천 1 | 조회 141
dra**** 2021.08.15 1 141
19717
[엑셀 VBA] 데이터 자동화 - 웹에서 제공하는 MySQL Sever를 이용한 자동화 개념 (15)
dra**** | 2021.08.05 | 추천 2 | 조회 343
dra**** 2021.08.05 2 343
19715
[엑셀 VBA] 개체 Reference - APPLICATION Object 첨부파일 (2)
dra**** | 2021.08.05 | 추천 0 | 조회 117
dra**** 2021.08.05 0 117