Sub フォルダ内のExcelブックを集計する()
    Dim folderPath As String
    Dim formulaText As String
    Dim ws As Worksheet
    Dim lo As ListObject

    folderPath = ThisWorkbook.Path & "\PQ集計用"

    formulaText = _
        "let" & vbCrLf & _
        "    ソース = Folder.Files(""" & folderPath & """)," & vbCrLf & _
        "    Excelファイル = Table.SelectRows(ソース, each [Extension] = "".xlsx"")," & vbCrLf & _
        "    必要列 = Table.SelectColumns(Excelファイル, {""Name"", ""Content""})," & vbCrLf & _
        "    ブック読込 = Table.AddColumn(必要列, ""データ"", each Excel.Workbook([Content], true))," & vbCrLf & _
        "    シート抽出 = Table.AddColumn(ブック読込, ""シートデータ"", each Table.SelectRows([データ], each [Kind] = ""Sheet""){0}[Data])," & vbCrLf & _
        "    元ブック追加 = Table.AddColumn(シート抽出, ""変換後"", each Table.AddColumn([シートデータ], ""元ブック"", (x) => [Name]))," & vbCrLf & _
        "    列順変更 = Table.AddColumn(元ブック追加, ""整形後"", each Table.ReorderColumns([変換後], {""元ブック"", ""伝票No"", ""日付"", ""顧客名"", ""売上金額"", ""担当者名""}))," & vbCrLf & _
        "    縦結合 = Table.Combine(列順変更[整形後])," & vbCrLf & _
        "    型変換 = Table.TransformColumnTypes(縦結合, {{""元ブック"", type text}, {""伝票No"", Int64.Type}, {""日付"", type date}, {""顧客名"", type text}, {""売上金額"", Int64.Type}, {""担当者名"", type text}})" & vbCrLf & _
        "in" & vbCrLf & _
        "    型変換"

    ThisWorkbook.Queries.Add Name:="集計", Formula:=formulaText

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Set lo = ws.ListObjects.Add( _
        SourceType:=0, _
        Source:=Array("OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""集計"";"), _
        Destination:=ws.Range("A1"))

    lo.Name = "T_集計"

    With lo.QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [集計]")
        .Refresh BackgroundQuery:=False
    End With

    lo.ListColumns("売上金額").DataBodyRange.NumberFormatLocal = "#,##0"

    ThisWorkbook.Queries("集計").Delete
End Sub


