VBAでPower Queryのクエリを作成・削除する方法
Power Queryは大変便利な仕組みで、VBAからも扱える強力なものですが、社内研修をやってみて分かったことですが、ハードルが高いようです。Access使いならばそこまでじゃないのですが、そもそもAccessも挫折者が多数いることで有名。やはりクエリというものがなかなか理解できないようです。
しかし、アプリとして利用する場合大幅にVBAコードの記述を削減出来るので作り手としては使いたい所。ということで、コマンド一発で必要なクエリを構築する事が可能になり、直接クエリを弄らなくても、クエリの作成削除ができる。ということで、今回はVBAからPower Queryのクエリを作ってみたいと思います。
今回使用するファイル等
Power Queryでの様々な使い方や作り方については、以下の講座で詳細に記述しているので、参考にしてみてください。ダミーデータはMockarooにて生成しています。
仕組み
前述のExcelで身につけるべきスキルコース（松）でも紹介していますが、同じディレクトリ内にあるdatabase.xlsx内にあるテーブルに相対パスで接続して、クエリを作成するという仕組みを今回は利用しています。同じブック内のテーブルではないので注意。
- excelpathシートにPower Queryでクエリを作るブックが存在するフォルダのフルパスを作る関数を記述。
//カレントディレクトリのフルパスを取得する数式 =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1) - この関数のセルは名前付き範囲にて、excelmanと命名してあります。
- 普通にクエリを外部接続で作ると以下のようなクエリになる。database.xlsxのテーブル名はcompmanと命名。
let ソース = Excel.Workbook(File.Contents("C:\Users\username\Desktop\database.xlsx"), null, true), compman_Table = ソース{[Item="compman",Kind="Table"]}[Data], 変更された型 = Table.TransformColumnTypes(compman_Table,{{"id", Int64.Type}, {"compname", type text}, {"address", type text}}) in 変更された型 - 上記のソースを変更して、以下のような相対パスの接続に変更する。ファイルパスでexcelmanの値を取得してソースで結合させています。
let ファイルパス = Excel.CurrentWorkbook(){[Name="excelman"]}[Content]{0}[Column1], ソース = Excel.Workbook(File.Contents(ファイルパス & "\database.xlsx"), null, true), compman_Table = ソース{[Item="compman",Kind="Table"]}[Data], 変更された型 = Table.TransformColumnTypes(compman_Table,{{"id", Int64.Type}, {"compname", type text}, {"address", type text}}) in 変更された型
詳細エディタを開いて編集が必要なので、よりハードルが高くなっているゆえに、例えば結合テーブルを作る場合に於いて、対象テーブルが増減する場合、そのテーブル分作る作業、そして結合して１枚にしてるクエリの手直しがその都度必要になる。
今回のVBAはこれらを不要にし、ボタンクリックのみでクエリを作成して出力するまでを装備したいと思います。一度手動で作ってみて、上記のようなコードを得てから構築すると楽に作れると思います。
図：フォルダのフルパスを取得させている
ソースコード
単純なクエリの作成
database.xlsxの中にあるcompmanテーブルに接続し、単純に出力するだけのクエリを単発で作ってみようと思います。クエリの追加は、ActiveWorkbook.Queries.Addというメソッドを利用して作成する事ができ、詳細エディタの中身は直接記述する事が可能です。よって、加工する手順などもコード内に直接記述する事で、単純なテーブル呼び出し後の加工まで含めたクエリを構築可能です。
'クエリを作るVBA
Public Function createQueryTable()
'対象のテーブルの名称
Dim tableman As String
tableman = "compman"
'クエリの名称
Dim queryname As String
queryname = "companyman"
'単発のクエリを作成する
ActiveWorkbook.Queries.Add Name:=queryname, Formula:= _
"let" & Chr(13) & Chr(10) & _
" ファイルパス = Excel.CurrentWorkbook(){[Name=""excelman""]}[Content]{0}[Column1]," & Chr(13) & _
" ソース = Excel.Workbook(File.Contents(ファイルパス & ""\database.xlsx""), null, true)," & Chr(13) & _
" " & tableman & "_Table = ソース{[Item=""" & tableman & """,Kind=""Table""]}[Data]," & Chr(13) & _
" 変更された型 = Table.TransformColumnTypes(" & tableman & "_Table,{{""id"", Int64.Type}, {""compname"", type text}, {""address"", type text}})" & Chr(13) & _
"in" & Chr(13) & Chr(10) & _
" 変更された型"
'クエリの出力(exptableシートのA1に出力)
Dim sheetman As Worksheet
Set sheetman = ThisWorkbook.Worksheets("exptable")
'出力先シートをアクティブにしておく
ThisWorkbook.Worksheets("exptable").Select
With sheetman.ListObjects.Add(SourceType:=0, Source:="OLEDB;" & _
"Provider=Microsoft.Mashup.OleDb.1;" & _
"Data Source=$Workbook$;" & _
"Location=" & queryname & ";Extended Properties=""""", _
Destination:=Range("$A$1"))
With .QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [" & queryname & "]")
.ListObject.DisplayName = tableman
.Refresh BackgroundQuery:=False
End With
End With
'終了処理
MsgBox "テーブルが作成されました"
End Functionも
- 詳細エディタで開いた時のM言語の構文自体をVBAで構築し、それをもって、Queries.Addで作成します
- クエリ名や対象のテーブル名などは変数としておき、それらを組み込んでいます。
- 同一名称のクエリがあるとエラーになるので注意が必要です。
- exptableというシートに対して出力をさせています。
- Locationでquerynameを指定し、CommandTextでもquerynameを指定します。
- ListObjects.Addでテーブルとして対象のシートに出力をするのにOLEDBでの接続が必要です。
- ListObject.DisplayNameが出力した際のテーブル名となります。
- Refresh BackgroundQueryでFalseを指定してるので、VBAで更新する場合更新が完了するまで返さないようになります。
注意すべき点は、最後のシートへの書き出し。書き出し先シートとしてsheetmanで指定していますが、これとは別にシートをアクティブにするThisWorkbook.Worksheets(シート名).Selectを記述してアクティブにしておかないと、「実行時エラー 1004 テーブルのワークシートデータはテーブルと同じシート上になければなりません」というエラーが出て止まってしまいます。必ずシートをアクティブにしてから実行しましょう。
図：おかしなエラーが出るので注意
図：作成と同時に出力まで完了した
シートデータに基づいて複数のテーブルを結合
シートに接続するワークブックおよびテーブル名を元に連続的にクエリを作成し、それらを元に１枚に統合したクエリというものを作成してみたいと思います。今回はdatabase.xlsx、database2.xlsxのcompmanというテーブルに接続し、それぞれに名前を付けてクエリを作る。この2つのクエリを元に結合するクエリを作成するといった手順になります。
※予め、qrylistというシートにクエリ名、ファイル名を記述しておく必要があります。
'ファイルリストに基づいて結合クエリを作る
Public Function createBindQuery()
'filelistテーブルのデータを取得する
Dim arr
Dim reccnt As Integer
Dim arrcnt As Integer
With ThisWorkbook.Worksheets("qrylist")
arr = .UsedRange
reccnt = .UsedRange.Rows.Count
arrcnt = reccnt - 2 'タイトル列を除外し、0からスタートするため
End With
'出力先テーブルをクリアする
ThisWorkbook.Worksheets("exptable").Cells.Clear
'クエリをすべて削除する
'ワークシートに接続
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim qry As WorkbookQuery
For Each qry In wb.Queries
qry.Delete
Next
'対象のテーブルの名称
Dim tableman As String
tableman = "compman"
'最終出力するクエリ名
Dim expquery As String
expquery = "結合クエリ"
Dim expname As String
expname = "bindquery"
'データを取得する
Dim queryname As String
Dim bookname As String
Dim dblist() As String
Dim cnt As Integer
cnt = 0
'配列の数を指定する
ReDim Preserve dblist(arrcnt)
For i = 2 To reccnt
'クエリ名とファイル名を取得
queryname = arr(i, 1)
bookname = "\" & arr(i, 2)
'配列に追加する
dblist(cnt) = arr(i, 1)
cnt = cnt + 1
'クエリを作成する
ActiveWorkbook.Queries.Add Name:=queryname, Formula:= _
"let" & Chr(13) & Chr(10) & _
" ファイルパス = Excel.CurrentWorkbook(){[Name=""excelman""]}[Content]{0}[Column1]," & Chr(13) & _
" ソース = Excel.Workbook(File.Contents(ファイルパス & """ & bookname & """), null, true)," & Chr(13) & _
" " & tableman & "_Table = ソース{[Item=""" & tableman & """,Kind=""Table""]}[Data]," & Chr(13) & _
" 変更された型 = Table.TransformColumnTypes(" & tableman & "_Table,{{""id"", Int64.Type}, {""compname"", type text}, {""address"", type text}})" & Chr(13) & _
"in" & Chr(13) & Chr(10) & _
" 変更された型"
Next i
'dblistをカンマ区切りに変換
Dim arrstr As String
arrstr = Join(dblist, ",")
'結合クエリを作成する
ActiveWorkbook.Queries.Add Name:=expname, Formula:= _
"let" & Chr(13) & Chr(10) & _
" ソース = Table.Combine({" & arrstr & "})" & Chr(13) & _
"in" & Chr(13) & Chr(10) & _
" ソース"
'クエリの出力(exptableシートのA1に出力)
Dim sheetman As Worksheet
Set sheetman = ThisWorkbook.Worksheets("exptable")
'出力先シートをアクティブにしておく
ThisWorkbook.Worksheets("exptable").Select
With sheetman.ListObjects.Add(SourceType:=0, Source:="OLEDB;" & _
"Provider=Microsoft.Mashup.OleDb.1;" & _
"Data Source=$Workbook$;" & _
"Location=" & expname & ";Extended Properties=""""", _
Destination:=Range("$A$1"))
With .QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [" & expname & "]")
.ListObject.DisplayName = expquery
.Refresh BackgroundQuery:=False
End With
End With
'終了処理
MsgBox "結合クエリを作成しました"
End Function
- ワークシート上のリストデータに基づいて個別のファイルのcompmanテーブルへのクエリを作成しています
- 個別のクエリ名は配列に格納し、後で作成する結合クエリで利用します。
- 作成した個別のクエリを１枚の結合クエリとして合体させています（bindqueryという名称を付与）
- bindqueryをテーブル名「結合クエリ」として、exptableシートに出力しています。クエリの中身は以下のような感じ。db1とdb2はクエリ名。
let ソース = Table.Combine({db1,db2}) in ソースCombineの引数はクエリ名をカンマ区切りにする必要があるため、予め用意しておいたdblistという配列をJoinでカンマ区切りに変換して渡しています。この配列はタイトル列分と0から始まるため、データの全カウントから-2した値でRedim Preserveで配列数を指定しています。
- 関数実行前にexptableシートの内容の全クリアと、作成済みクエリの全削除を実行しています。
図：結合したクエリだけを出力してみました
クエリの削除
Power Queryのメソッドには作成やリフレッシュ等のメソッドはあるものの、既存のクエリのUpdateといったメソッドが見当たらない。故に既に存在してるクエリを変更したい場合は、「一度対象のクエリを削除してから、クエリを作り直す」という作業が必要です。
その為には、作成をする前に一度、対象のクエリをDeleteする仕組みが必要になります。以下のような構文で対象のクエリだけを削除しています。
'特定のクエリのみ削除する
Public Function deleteQueryTable()
'ワークシートに接続
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim qry As WorkbookQuery
'削除するクエリ名
Dim queryname As String
Dim tempquery As String
queryname = "companyman"
'削除の実行
For Each qry In wb.Queries
'クエリ名を取得する
tempquery = qry.Name
'判定する
If tempquery = queryname Then
'クエリを削除する
qry.Delete
End If
Next
'終了処理
MsgBox "クエリ削除処理が完了しました"
End Function
- companymanというクエリの削除だけを行っています
- wb.Queriesでワークブック内のクエリ一覧をForループで取得しています
- 指定したクエリ名とtempqueryの名称が一致したら、qry.Deleteで対象のクエリを削除しています。
