Excel VBA Power Query クエリ作成汎用ツール

前回の VBA で Power Query サポート無しでフォルダーから取得 の続編です。前回はCSVデータの取得でしたが、フォルダ内のエクセルデータの取得や単体のデータからもクエリを作成できるように変更を加えていきたいと思います。

くるみこ
くるみこ

VBAでクエリの基本部分だけつくるならば、Mコードさえわかれば意外と簡単に作れることが
わかりました。今回はもっと汎用的に使えるように対応の幅を広げてみましょう。

いろいろな種類に対応できたら最高ですね。どんなふうに設定するのか楽しみです。
よろしくお願いしますm(__)m

【この記事でわかることは】
・VBAで Power Query の CSVとExcelブックから基本クエリを作成する方法
・[フォルダーから] と [ファイルから] の両方に対応できるようにする方法

VBA で Power Query の サポートクエリ無しでフォルダーから取得する記事がこちらです。

はじめに動作の設定を検討します

基本的な動作の設定を決めておきましょう。

・対象とするデータが、CSVファイルとExcelブックとします。
・[フォルダーから] と [ファイルから] の両方に対応できるようにします。
・設定はパラメータークエリを使って設定することとします。
・クエリ名と作成対象の指定は、Application.InputBox で選択させることとします。
・フィルター、ヘッダー昇格や型変更などの細かい設定は行わないようにします。
・詳細な設定は出来上がった基本的なクエリをもとに手作業で行ってください。

設定に使う「Mコード」を確認します

VBAに組み込む「Mコード」のそれぞれ必要な部分を取得しておきましょう。

[フォルダから] CSVを取得結合する Mコード

let
    ソース = Folder.Files("D:\Users\Desktop\CSV"),
    ファイル変換 = Table.AddColumn(ソース, "CSV変換", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=",",
        Encoding=932, QuoteStyle=QuoteStyle.None]))),
    不要な列を削除 = Table.RemoveColumns(ファイル変換,{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    名前が変更された列 = Table.RenameColumns(不要な列を削除,{{"Name", "FileName"}})
in
    名前が変更された列

[フォルダから] Excelブックを取得結合する Mコード

let
    ソース = Folder.Files("D:\Users\Desktop\エクセル"),
    ファイル変換 = Table.AddColumn(ソース, "XLS変換", each Excel.Workbook([Content],true){[Item="Sheet1",Kind="Sheet"]}[Data]),
    不要な列を削除 = Table.RemoveColumns(ファイル変換,{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"名前が変更された列 " = Table.RenameColumns(不要な列を削除,{{"Name", "Source.Name"}})
in
    #"名前が変更された列 "

[CSVから] データを取得する Mコード

let
    ソース = Csv.Document(File.Contents("D:\Users\Desktop\CSV210601.csv"),[Delimiter=",", Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"販売日", type date}, {"エリア", type text}, {"品名", type text}, {"数量", Int64.Type}, {"金額", Int64.Type}, {"店舗コード", type text}})
in
    変更された型

[ブックから] データを取得する Mコード

let
    ソース = Excel.Workbook(File.Contents("D:\Users\Desktop\エクセル210602.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"販売日", type date}, {"エリア", type text}, {"品名", type text}, {"数量", Int64.Type}, {"金額", Int64.Type}, {"店舗コード", type text}})
in
    変更された型

パラメータークエリ用のテーブルを設定

パラメーター用のテーブルは次のように設定ておきます。

「クエリ作成実行」が「実効」になっているのはご愛嬌ということで(^^ゞ

Name = ここにはクエリ名を設定しておいてもいいと思います。
      1行目だけは自ブックのパスを自動取得する設定になっています。

Ext = フォルダ指定の場合は「拡張子」、ファイル指定の場合はその種別を選択します。
  Item = Excelの場合はシート名またはテーブル名を入力します。
  Kind = Sheet か Table を指定します。
  ※ Item と Kind の指定漏れ対策用にVBAでデフォルトの設定をしておくこととします。

VBAコードを設定しましょう

・準備ができたので基本的なクエリを作成するVBAのコードを作成しましょう。
・基本部分の Mコード の内容にパラメーター値に入れ込むようにしていきます。
・VBAでは パラメーター値の部分は、テーブルからデータを習得して適用します。

VBAで基本的なクエリを作成するコード

・プロシージャを分割しなかったので少し長くなっています。

'VBAでクエリを作成するサンプル(フォルダー&ファイル)
'フォルダーからはサンプルクエリなしで取得します
Sub AddQueryGetData()
    Dim qname As String         'クエリ名用
    'クエリ名はインプットボックスで入力指定を可能にする
    qname = Application.InputBox( _
        "作成するクエリ名前を入力または選択してください!", "クエリ名設定", "Sample")
    If qname = "False" Then Exit Sub        '指定のない場合中止
    
    Dim sourcePath As String    'ソース指定用
    'Sourceを選択指定させる
    sourcePath = Application.InputBox( _
        "ソースパスとする「Name」セル選択してください!", "ソースパス名指定", "")
    If sourcePath = "False" Then Exit Sub   '指定のない場合中止
    
    'ソースパスのRangeを取得
    Dim rng As Range
    Set rng = Range("A:A").Find(sourcePath)
    If rng Is Nothing Then MsgBox "設定がみつかりませんでした。": Exit Sub
    sourcePath = Chr(34) & sourcePath & Chr(34) 'ソースパスに""付加
    
    'ソースが絶対パスかどうかを確認して処理を分岐する
    Dim fso As Object   'FileSystemObjectを使う
    Dim sPath As String, sExt As String
    Dim sItem As String, sKind As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    sPath = rng.Offset(0, 1).Value  'ソースのパス指定
    sExt = rng.Offset(0, 2).Value   'ソースの拡張子の指定
    sItem = rng.Offset(0, 3).Value  '対象名(Sheet名,Table名 ?)
    If sItem = "" Then sItem = "Sheet1" '指定がない場合"Sheet1"
    sKind = rng.Offset(0, 4).Value  '対象の種類(Sheet,Table ?)
    If sKind = "" Then sKind = "Sheet"  '指定がない場合"Sheet"
    
    'FileSystemObjectを使ってパス指定を検証
    If sPath = fso.GetAbsolutePathName(sPath) Then
        '絶対パスの場合の処理
        sPath = "GetParamValue(" & sourcePath & ")"
    Else
        '相対パスなら
        sPath = "GetParamValue(""MyPath"") & GetParamValue(" & sourcePath & ")"
    End If
    Set fso = Nothing
    
    'フォルダ/ファイルの指定判定
    Select Case sExt
        Case "csv"
            'フォルダ内csvの場合のMコード
            sExt = "ソース, ""CSV変換"", " & _
                "each Table.PromoteHeaders(Csv.Document([Content],[Delimiter="",""," & _
                "Encoding=932, QuoteStyle=QuoteStyle.None]))),"
        Case "xls"
            'フォルダ内ブックの場合のMコード
            sExt = "ソース, ""XLS変換"", " & _
                "each Excel.Workbook([Content],true){[Item=""" _
                & sItem & """,Kind=""" & sKind & """]}[Data]),"
        Case "CSVファイル"
            'csv単一ファイルの場合のMコードをセット
            ActiveWorkbook.Queries.Add Name:=qname, Formula:= _
            "let" & Chr(13) & Chr(10) & _
            "    ソース = Csv.Document(File.Contents(" & sPath & "),[Delimiter="","", Columns=18, Encoding=932, QuoteStyle=QuoteStyle.None])," & Chr(13) & _
            "    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])" & Chr(13) & _
            "in" & Chr(13) & Chr(10) & _
            "    昇格されたヘッダー数 //ソース//変更された型"
            MsgBox "csvファイルのクエリを作成しました!": Exit Sub
        Case "Excelブック"
            'エクセル単一ファイルの場合のMコードをセット
            ActiveWorkbook.Queries.Add Name:=qname, Formula:= _
            "let" & Chr(13) & Chr(10) & _
            "    ソース = Excel.Workbook(File.Contents(" & sPath & "))," & Chr(13) & _
            "    " & sItem & "_" & sKind & " = ソース{[Item=""" & sItem & """,Kind=""" & sKind & """]}[Data]" & Chr(13) & _
            "//    昇格されたヘッダー数 = Table.PromoteHeaders(" & sItem & "_" & sKind & ", [PromoteAllScalars=true])" & Chr(13) & _
            "in" & Chr(13) & Chr(10) & _
            "    " & sItem & "_" & sKind & ""
            MsgBox "Excelブックのクエリを作成しました!": Exit Sub
        Case Else
            MsgBox "ファイル指定が不明のため中止します!": Exit Sub
    End Select
    'ここからはフォルダー指定の場合の処理
    'パラメーターによりフォルダ内ファイルを展開できるカスタム列作成までのMコードをセット
    ActiveWorkbook.Queries.Add Name:=qname, Formula:= _
    "let" & Chr(13) & Chr(10) & _
    "    ソース = Folder.Files(" & sPath & ")," & Chr(13) & _
    "    ファイル変換 = Table.AddColumn(" & sExt & Chr(13) & _
    "    不要な列を削除 = Table.RemoveColumns(ファイル変換,{""Content"", ""Extension"", ""Date accessed"", " & _
                            """Date modified"", ""Date created"", ""Attributes"", ""Folder Path""})," & Chr(13) & _
    "    名前が変更された列 = Table.RenameColumns(不要な列を削除,{{""Name"", ""FileName""}})" & Chr(13) & _
    "in" & Chr(13) & Chr(10) & _
    "    名前が変更された列"
    MsgBox sourcePath & "のクエリ作成処理を完了しました!": Exit Sub
End Sub

・文字列が長いので途中で一部改行を入れています。

・Application.InputBox でクエリ名を指定できるようにしています。

・6行目、12行目とインプットボックスが2回表示されます。

・初めが「クエリ名」指定、入力かセル選択で指定します。

・次がパラメーターテーブルの「Name」列からクエリを作成する行を指定します。

・18行目で、指定されたセルのRangeを取得しています。

・20行目は、パラメーターの「Name」にダブルクォーテーションを設定しています。
 これは、Mコードに適用するために必要な設定です。

・27~32行で、Mコードに適用するためのデータをテーブルから取得して変数に代入しています。

・35~41行で、指定されたパスが相対パスか絶対パスかを FileSystemObject で調べてパスを調整しています。(相対パスの場合は自ブックのパスと合わせてフルパスにしています)

・45行目からの処理は、Select Case を使って [フォルダーから] と [ファイルから] で分岐処理しています。

・それぞれに該当する Mコード を ActiveWorkbook.Queries.Add に適用してクエリを作成します。

・46行目の Case “csv” と 51行目の Case “xls” では、フォルダーからデータ取得・結合に適用する Mコード の設定を変数 sExt にセットしています。

・80行目で、フォルダーからデータ取得・結合するクエリを作成しています。

VBA実行時の動作を確認します

・動作確認を実行したところ、すべての種類で無事に基本クエリを作成できました。

・そのうちの一つだけGIF画像にしましたので次に紹介します。

フォルダーからExcelブックのデータを取得するサンプル画像

・シェイプの実行ボタン押下後、初めの InputBox でクエリ名をセル選択で設定しました。
・次の InputBox でクエリ設定用の「Name」列該当行のセルを選択しています。
 ※セル選択時点ではInputBoxのテキストボックスには選択セルアドレスが表示されています!

・クエリ作成後にPowerQueryエディターの編集でカスタム列「XLS変換」を展開しています。

・その他のクエリ作成でも同じ様な感じです。

・ただ、Excelブックから作成するクエリでは、あえて「ヘッダーの昇格」部分をカットしています。
 それは、Excelブックの場合先頭列にタイトルがあったりする場合が多いので(^^;

・「パラメータークエリ」を作成する方法はこちらの記事をご覧ください。

スポンサーリンク

まとめ(おわりに)

以上、VBAを使って Power Query の [フォルダーから] と [ファイルから] のデータ取得に対応した基本クエリ作成ツールの作成ついて紹介しました。「設定」シートのパラメータークエリをさらに拡張して使っています。前回よりも汎用的に使用できるようになったと思います。

サンプルファイルをダウンロードできるように登録していますのでご利用ください。

まとめと感想など

くるみこ
くるみこ

VBAを使った Power Query の基本クエリを作成するツールが完成しましたね。いかがでしたか? 「フィルター」を設定したり、「ヘッダーの昇格」や「型変更」なども追加できますが、汎用で使うためにその部分はあえてカットしています。

わかりました(^^)/ でもカットした部分を個別に VBA の Mコード 部分に追加すれば仕様の変更が可能ということですね。 これが「汎用」で使えるということですね。

【今回わかったことは】
・VBAで Power Query の CSVとExcelブックを [フォルダーから] と [ファイルから] の両方に対応できるようにした基本クエリを作成する方法がわかりました

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

今回記事のサンプルファイルを登録しています
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください