【PowerQuery+VBA】ファイル名・テーブル名が毎回かわるデータソースに対応して変化に強くなる

サムネイル

パワークエリ、めちゃくちゃ便利で最近毎日開いて鬼リピしているのですが、
データソースのパスが変わった際はいちいちパワークエリエディタを開いて編集しなければならず厄介でした。

さらに業務で利用したいデータソースが
Accessファイル名とテーブル名が毎回変わるソース(変わるのが日付名だけとは限らないのでイライラ…)で…
変えるのめんどくせーとおもっていたのですが、以下の方法で解決させました。
シート名が毎回変わるエクセルソースとかでも有効だと思います。

▼解決方法

以下のデータソースを相対参照で設定する方法からヒントを得て
ファイル名やテーブル名も変数的に設定すれば簡単に対応できましたので備忘録。
www.y-shinno.com

以下の方法でコンフィグシートを作成します。

①フォルダパスの指定

見に行くフォルダは固定なので、
↑の相対参照の方法と同じ考え方でセルに名前を定義して
フォルダパスを設定します。
この例では分けてますがそれぞれA2を"FilePath"A5を"AccessFld"としてセルの名前を定義しています。

②ファイル名指定

指定したフォルダの中に複数ファイルがあるので、自分で選択できるようにします。

指定したフォルダの中から存在するファイル名をVBAで取得して、
ファイル名一覧をテーブル化してから、定義されたセルでプルダウンリストにします。
テーブル化することでINDIRECT関数で簡単にプルダウンを作れます。
www4.synapse.ne.jp

パワークエリは取り込み形式も指定しないとエラーになってしまうので、
ファイル名取り出し時は拡張子を絞ってあげるとよきです。
この例ではA8を"AccessFile"とセルの名前を定義しています。

▼②のボタンに設定するファイル名取得のVBA
※あらかじめファイル名を入れるテーブルを作っておきます。(この例ではオレンジの表)

Sub GetFlName()

'フォルダ内にAccessがあるか確認する
    If Dir(Range("▼▼①で指定したフォルダパスのセルの定義名▼▼").Value & "\" & "*.accdb") = "" Then
        MsgBox "データベースがありませんでした。フォルダパスを確認してください。", vbInformation
        Exit Sub
    End If

'ファイル名一覧をテーブルに格納する
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("▼▼ファイル名のテーブルがあるシート名▼▼")
    Dim myTable As ListObject
    Set myTable = ws.ListObjects("▼▼ファイル名を入れるテーブル名▼▼")
    If Not (myTable.DataBodyRange Is Nothing) Then
        myTable.DataBodyRange.Delete
    End If

    Dim buf As String, cnt As Long
    buf = Dir(Range("▼▼①で指定したフォルダパスのセルの定義名▼▼").Value & "\" & "*.accdb")
    Do While buf <> ""
        cnt = cnt + 1
        Debug.Print buf
        ws.Cells(cnt + 1, ▼▼ファイル名のテーブルの列番号▼▼) = buf
        buf = Dir()
    Loop
    
End Sub

③テーブル名指定

②で選択したファイルを読み込んで、今度はテーブル名を選択できるようにします。
テーブルが複数ある場合、PowerQueryがファイルを読み込んだ後に
テーブル名の一覧が出てくるのでそれをエクセルシートに表示させます。
これも定義されたセルでプルダウンリストにします。
この例ではA11を"TableName"とセルの名前を定義しています。

▼まずはテーブル名取り込み用のクエリを組みます。
PowerQueryエディタで①②からデータを取り込むように設定します。

▼テーブル取得用クエリの詳細エディタの状態

let
    ファイルパス = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    フォルダ名 =  Excel.CurrentWorkbook(){[Name="AccessFld"]}[Content]{0}[Column1],
    ファイル名 =  Excel.CurrentWorkbook(){[Name="AccessFile"]}[Content]{0}[Column1],
    ソース = Access.Database(File.Contents(ファイルパス & フォルダ名 & ファイル名), [CreateNavigationProperties=true])
in
    ソース

これを設定するとこんな感じでテーブル名が表示されるので

そのままシートに取り込みます。

次に最新のテーブル名をとってこれるようにクエリ更新用のVBAを組みます。

▼③のボタンに設定するテーブル名取得のVBA

Sub GetTblName()
　ThisWorkbook.Connections(" ▼▼テーブル取得用クエリ名▼▼").Refresh
End Sub

ちなみにクエリ名ですがプロパティをみると「クエリ -」と頭についていることがあるので注意。

これで準備完了！
取り込みたいデータを表示させるためのクエリを作ります。
最初の部分だけ以下にして、あとは加工ご自由に！
これで毎回オレンジ、黄色、水色のボタンを押していけば変化に強いPowerQueryライフを楽しめます！

▼最終表示用クエリの詳細エディタ

let
    テーブル名 =  Excel.CurrentWorkbook(){[Name="TableName"]}[Content]{0}[Column1],
    ソース = ▼▼テーブル取得用クエリ名▼▼,
    データ = ソース{[Schema="",Item=テーブル名]}[Data]
in
    データ

▼取り込み用のボタンに設定するVBA

Sub GetTbl()
　ThisWorkbook.Connections(" ▼▼最終表示用クエリ名▼▼").Refresh
End Sub

できた！