VBAでPower Queryのクエリを作成・削除する方法

Power Queryは大変便利な仕組みで、VBAからも扱える強力なものですが、社内研修をやってみて分かったことですが、ハードルが高いようです。Access使いならばそこまでじゃないのですが、そもそもAccessも挫折者が多数いることで有名。やはりクエリというものがなかなか理解できないようです。

しかし、アプリとして利用する場合大幅にVBAコードの記述を削減出来るので作り手としては使いたい所。ということで、コマンド一発で必要なクエリを構築する事が可能になり、直接クエリを弄らなくても、クエリの作成削除ができる。ということで、今回はVBAからPower Queryのクエリを作ってみたいと思います。

今回使用するファイル等

Power Queryでの様々な使い方や作り方については、以下の講座で詳細に記述しているので、参考にしてみてください。ダミーデータはMockarooにて生成しています。

Excelで身に付けるべきスキルコース(松)

仕組み

前述のExcelで身につけるべきスキルコース(松)でも紹介していますが、同じディレクトリ内にあるdatabase.xlsx内にあるテーブルに相対パスで接続して、クエリを作成するという仕組みを今回は利用しています。同じブック内のテーブルではないので注意。

  • excelpathシートにPower Queryでクエリを作るブックが存在するフォルダのフルパスを作る関数を記述。
  • この関数のセルは名前付き範囲にて、excelmanと命名してあります。
  • 普通にクエリを外部接続で作ると以下のようなクエリになる。database.xlsxのテーブル名はcompmanと命名。
  • 上記のソースを変更して、以下のような相対パスの接続に変更する。ファイルパスでexcelmanの値を取得してソースで結合させています。

詳細エディタを開いて編集が必要なので、よりハードルが高くなっているゆえに、例えば結合テーブルを作る場合に於いて、対象テーブルが増減する場合、そのテーブル分作る作業、そして結合して1枚にしてるクエリの手直しがその都度必要になる。

今回のVBAはこれらを不要にし、ボタンクリックのみでクエリを作成して出力するまでを装備したいと思います。一度手動で作ってみて、上記のようなコードを得てから構築すると楽に作れると思います。

図:フォルダのフルパスを取得させている

ソースコード

単純なクエリの作成

database.xlsxの中にあるcompmanテーブルに接続し、単純に出力するだけのクエリを単発で作ってみようと思います。クエリの追加は、ActiveWorkbook.Queries.Addというメソッドを利用して作成する事ができ、詳細エディタの中身は直接記述する事が可能です。よって、加工する手順などもコード内に直接記述する事で、単純なテーブル呼び出し後の加工まで含めたクエリを構築可能です。

  • 詳細エディタで開いた時のM言語の構文自体をVBAで構築し、それをもって、Queries.Addで作成します
  • クエリ名や対象のテーブル名などは変数としておき、それらを組み込んでいます。
  • 同一名称のクエリがあるとエラーになるので注意が必要です。
  • exptableというシートに対して出力をさせています。
  • Locationでquerynameを指定し、CommandTextでもquerynameを指定します。
  • ListObjects.Addでテーブルとして対象のシートに出力をするのにOLEDBでの接続が必要です。
  • ListObject.DisplayNameが出力した際のテーブル名となります。
  • Refresh BackgroundQueryでFalseを指定してるので、VBAで更新する場合更新が完了するまで返さないようになります。

注意すべき点は、最後のシートへの書き出し。書き出し先シートとしてsheetmanで指定していますが、これとは別にシートをアクティブにするThisWorkbook.Worksheets(シート名).Selectを記述してアクティブにしておかないと、「実行時エラー 1004  テーブルのワークシートデータはテーブルと同じシート上になければなりません」というエラーが出て止まってしまいます。必ずシートをアクティブにしてから実行しましょう。

図:おかしなエラーが出るので注意

図:作成と同時に出力まで完了した

シートデータに基づいて複数のテーブルを結合

シートに接続するワークブックおよびテーブル名を元に連続的にクエリを作成し、それらを元に1枚に統合したクエリというものを作成してみたいと思います。今回はdatabase.xlsx、database2.xlsxのcompmanというテーブルに接続し、それぞれに名前を付けてクエリを作る。この2つのクエリを元に結合するクエリを作成するといった手順になります。

※予め、qrylistというシートにクエリ名、ファイル名を記述しておく必要があります。

  • ワークシート上のリストデータに基づいて個別のファイルのcompmanテーブルへのクエリを作成しています
  • 個別のクエリ名は配列に格納し、後で作成する結合クエリで利用します。
  • 作成した個別のクエリを1枚の結合クエリとして合体させています(bindqueryという名称を付与)
  • bindqueryをテーブル名「結合クエリ」として、exptableシートに出力しています。クエリの中身は以下のような感じ。db1とdb2はクエリ名。

    Combineの引数はクエリ名をカンマ区切りにする必要があるため、予め用意しておいたdblistという配列をJoinでカンマ区切りに変換して渡しています。この配列はタイトル列分と0から始まるため、データの全カウントから-2した値でRedim Preserveで配列数を指定しています。
  • 関数実行前にexptableシートの内容の全クリアと、作成済みクエリの全削除を実行しています。

図:結合したクエリだけを出力してみました

クエリの削除

Power Queryのメソッドには作成やリフレッシュ等のメソッドはあるものの、既存のクエリのUpdateといったメソッドが見当たらない。故に既に存在してるクエリを変更したい場合は、「一度対象のクエリを削除してから、クエリを作り直す」という作業が必要です。

その為には、作成をする前に一度、対象のクエリをDeleteする仕組みが必要になります。以下のような構文で対象のクエリだけを削除しています。

  • companymanというクエリの削除だけを行っています
  • wb.Queriesでワークブック内のクエリ一覧をForループで取得しています
  • 指定したクエリ名とtempqueryの名称が一致したら、qry.Deleteで対象のクエリを削除しています。

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。