VBA で PowerQuery サポート無しでフォルダー取得クエリ作成

Power Query でフォルダーからデータを取得する場合、通常は「サポートクエリ」を使ってうまく作成できるようになっています。でも、この「サポートクエリ」って結構邪魔なんですよね。

そこで、VBAを使って「サポートクエリ」無しで [フォルダーから] データ取得するクエリを作成できるツールを作成したいと思います。

くるみこ
くるみこ

「サポートクエリ」なしで「フォルダーから」結合する方法を勉強していますが、カスタム列を追加する部分までをVBAで簡単に設定できないかやってみましょう。

VBAでクエリをつくるのってできるんですか?
VBAで簡単につくれようになったらうれしいですね。
よろしくお願いしますm(__)m

【この記事でわかることは】
・VBAで Power Query [フォルダーから] データ取得クエリを作成する方法

「サポートクエリ」なしで「フォルダーから」結合する方法についての記事がこちらです。

Power Query での動作を確認します

Power Query のクエリ作成手順で [データの取得] > [ファイルから] > [フォルダーから] と指定した場合、普通の手順で進めていくと「サポートクエリ」が作成されてフォルダ内のファイルが結合される仕組みになっているんですよね。

「マクロの記録」と「Mコード」を確認します

でも、「ソース」を設定してフォルダー内のファイル取得後に「サポートクエリ」を使わずに「カスタム列」を追加してファイルを結合するところまでの基本動作をVBAにしていきます。

[マクロの記録] で記録されたコード

・「カスタム列」を追加するところまでを記録したコードです。(見やすくするため一部改行と空白行を削除しています)

Sub Macro1()
' Macro1 Macro
    ActiveWorkbook.Queries.Add Name:="08", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    ソース = Folder.Files(""D:\自ブックFolder\CSV21"")," & Chr(13) & "" & Chr(10) & _
        "    追加されたカスタム = Table.AddColumn(ソース, ""カスタム"", each [Content])" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & "    追加されたカスタム"
    Workbooks("MakeQuery.xlsm").Connections.Add2 "クエリ - 08", _
        "ブック内の '08' クエリへの接続です。", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=08;Extended Properties=""""" _
        , "SELECT * FROM [08]", 2
End Sub

出来上がったクエリのMコード

・「ソース」と「追加されたカスタム」二つのステップが記録されています。

let
    ソース = Folder.Files("D:\自ブックFolder\CSV21"),
    追加されたカスタム = Table.AddColumn(ソース, "カスタム", each [Content])
in
    追加されたカスタム

コードを見比べてわかったこと

「マクロの記録」で分かった部分

Workbook.Queries.Add メソッドでクエリを作成していること。
 構文:Workbook.Queries.Add(Name, Formula, Description)

名前必須 / オプションデータ型説明
Name必須Stringクエリの名前
Formula必須String新しいクエリの Power Query M 数式
Description省略可能Variantクエリの説明

Workbooks(“MakeQuery.xlsm”).Connections.Add2 メソッドで接続を設定しています。
 構文:Workbook.Add2(Name, Description, ConnectionString, CommandText, lcmdtype, createmodelconnection, importrelationships)

名前必須 /
オプション
データ型説明
Name必須String接続の名前を指定
Description必須String接続の簡単な説明を指定
ConnectionString必須Variant接続文字列を指定
CommandText必須Variant接続を作成する
コマンド テキストを指定
lCmdtype省略可能  XlCmdTypeコマンドの種類を指定
CreateModelConnection省略可能BooleanPowerPivot モデルへの接続を
作成するかどうかを指定
ImportRelationships省略可能Boolean既存のリレーションシップを
インポートするかどうかを指定
クエリのMコードで分かったこと

・接続を設定している Connections.Add2 メソッド の部分は含まれていないことがわかりました。


以上のことから、
単純に、Workbook.Queries.Add メソッドの Formula 部分に Power Query M 数式 を設定すればクエリを作成できることがわかりました。

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

・過去の記事でフォルダからCSVファイルのデータを取得するクエリのMコードがこちらです。このコードをVBAの Workbook.Queries.Add メソッドの Formula 部分に組み込んでみます。

let
    ソース = Folder.Files(GetParamValue("MyPath") & GetParamValue("Source")),
    #"ファイル変換" = 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"}}),
    展開されたヘッダー = Table.ExpandTableColumn(#"名前が変更された列", "CSV変換",{"販売日","品名","数量","店舗コード"}),
    変更された型 = Table.TransformColumnTypes(展開されたヘッダー,{{"販売日", type date}, {"品名", type text}, {"数量", Int64.Type}, {"店舗コード", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "曜日", each Date.ToText([販売日],"ddd")),
    変更された型1 = Table.TransformColumnTypes(追加されたカスタム,{{"曜日", type text}})
in
    変更された型1

・7行目の「展開されたヘッダー」以降のステップは、読み込むCSVファイルによって変わってくるので、6行目までを組み込むようにします。

クエリ「フォルダーから取得」を作成するVBA

・VBAでフォルダー内のCSVファイルデータを取得・結合するクエリを作成するコード

'フォルダーからCSVデータ取得を(サンプルクエリなしで)セット
Sub AddQueryGetFolder()
    Dim qname As String
    'クエリ名はインプットボックスで入力指定を可能にする
    qname = Application.InputBox("作成するクエリの名前を入力してください!", "クエリ名入力", "Sample")
    If qname = "False" Then Exit Sub
    'パラメーターによりフォルダ内ファイルを展開できるカスタム列作成までのMコードをセット
    ActiveWorkbook.Queries.Add Name:=qname, Formula:= _
    "let" & Chr(13) & Chr(10) & _
    "    ソース = Folder.Files(GetParamValue(""MyPath"") & GetParamValue(""Source""))," & Chr(13) & _
    "    ファイル変換 = Table.AddColumn(ソース, ""CSV変換"", " & _
                            "each Table.PromoteHeaders(Csv.Document([Content],[Delimiter="",""," & _
                            "Encoding=932, QuoteStyle=QuoteStyle.None])))," & 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) & _
    "    名前が変更された列"
End Sub

・文字列が長いので途中で改行を入れています。
・Application.InputBox でクエリ名を指定できるようにしています。
・「ソース」の指定は、パラメータークエリ「GetParamValue」から取得しています。
・下のGIF画像が実行後に作成できたクエリです。列「CSV変換」が展開できるようになっていることが確認できます。

・クエリの読み込み先の設定を確認したところ「接続の作成のみ」になっていました。
・「マクロの記録」に記録されていた接続の設定部分をVBAに入れる必要はなさそうです。
・接続先を「テーブル」にしたり「データモデルに追加する」などを設定する場合については、また別の機会まで保留にしておきます。

設定シートに実行ボタンを設置します

・実行ボタンはシェイプを使いマクロを登録しました。
・「設定」シートのテーブルを使ってパラメーターを設定しています。

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

・今回はCSVファイルの取得でしたが「ファイル変換」部分の設定を変更すれば他のファイルでも対応可能です。今後の記事でいろいろな設定を追加していきたいと思います。

スポンサーリンク

まとめ(おわりに)

以上、VBAを使って Power Query の [フォルダーから] データ取得するクエリを作成する方法について紹介しました。パラメータークエリを使っていますので「設定」シートで変更できますから汎用的に使用できると思います。

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

まとめと感想など

くるみこ
くるみこ

これでVBAを使って Power Query のクエリ作成方法がわかりましたね。いかがでしたか? 細かいクエリの設定は手動で行うことになりますけどね。次回以降で今回を他のクエリ作成等についても応用していけそうですね。

カスタム列でファイル変換を行う部分がVBAで実現できるのはすごくいいですね。

【今回わかったことは】
・VBAで Power Query [フォルダーから] データ取得クエリを作成する方法
・Workbook.Queries.Add メソッド の使い方がわかりました

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

【今後の記事について】

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

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

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

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