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

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

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

   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"

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

   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