【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
できた!