VBA技術解説PowerQueryの強力な機能をVBAから利用する方法
Excel2016より標準搭載されたPowerQueryはとても強力な機能ですが、使える環境が限られるので、頻繁に使われだすのはこれからになると思います。PowerQueryが広く使われだすと、色々な使い方が出てくると思います。ここでは、PowerQueryの優れた機能をVBAから利用する場合の手順を説明したものです。
'クエリの数式文字列作成 Dim qryStr1 As String Dim qryStr2 As String Dim qryStr3 As String Dim qryStr4 As String Dim qryStr5 As String qryStr1 = "let ソース = Excel.Workbook(QUERY2, null, true), Sheet1_Sheet = ソース[Data], 昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]) in 昇格されたヘッダー数" qryStr2 = "#""QUERY3"" meta [IsParameterQuery=true, BinaryIdentifier=#""QUERY3"", Type=""Binary"", IsParameterQueryRequired=true]" qryStr3 = "let ソース = Folder.Files(""" & _ strFolder & _ """), ナビゲーション1 = ソース[Content] in ナビゲーション1" qryStr4 = "let ソース = (QUERY2) => let ソース = Excel.Workbook(QUERY2, null, true), Sheet1_Sheet = ソース[Data], 昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]) in 昇格されたヘッダー数 in ソース" qryStr5 = "let ソース = Folder.Files(""" & _ strFolder & _ """), #""フィルター選択された非表示の File1"" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? true), カスタム関数の呼び出し1 = Table.AddColumn(#""フィルター選択された非表示の File1"", ""QUERY4"", each QUERY4([Content])), #""名前が変更された列 1"" = Table.RenameColumns(カスタム関数の呼び出し1, ), 削除された他の列1 = Table.Select" & "Columns(#""名前が変更された列 1"", QUERY4"">), 展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, ""QUERY4"", Table.ColumnNames(QUERY4(#""QUERY3""))), 変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,, , , , , >) i" & "n 変更された型" & ""
'ブックにクエリ追加 With wb.Queries .Add Name:="QUERY1", Formula:=qryStr1 .Add Name:="QUERY2", Formula:=qryStr2 .Add Name:="QUERY3", Formula:=qryStr3 .Add Name:="QUERY4", Formula:=qryStr4 .Add Name:="QUERY5", Formula:=qryStr5 End With
'接続文字列作成 Dim i As Long Dim Provider As String For i = 1 To 4 Provider = _ "OLEDB;" & _ "Provider=Microsoft.Mashup.OleDb.1;" & _ "Data Source=$Workbook$;" & _ "Location=QUERY" & i & ";" & _ "Extended Properties=""""" wb.Connections.Add2 _ "QUERY" & i, _ "接続" & i, _ Provider, _ "SELECT * FROM [QUERY" & i & "]", _ 2 Next
'クエリーテーブル追加 With ws.ListObjects.Add(SourceType:=0, _ Source:="OLEDB;" & _ "Provider=Microsoft.Mashup.OleDb.1;" & _ "Data Source=$Workbook$;" & _ "Location=QUERY5;Extended Properties=""""", _ Destination:=Range("$A$1")) With .QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [QUERY5]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "適当な名前" .Refresh BackgroundQuery:=False End With
'テーブルを範囲に変換 .TableStyle = "" .Unlist End With
'ブックのクエリを全削除、もちろん適宜変更 For Each qry In wb.Queries qry.Delete Next
Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True MsgBox "PowerQuery取込終了" Exit Sub
VBAコードの細かい解説は省略します。マクロの記録の元のVBAと比べながらVBAコードを読んでみてください。 処理の流れとしては、 ・QueryのFormulaを作成し、Queries.Add ・接続文字列を作成し、Connections.Add2 ・作成した接続でクエリテーブルを追加、ListObjects.Add 上記VBAコードの太字を中心に見ていくと流れがわかると思います。
クエリの数式文字列については、M言語と言うものになります。 これは説明していたらきりがありませんし、私もまだよく理解していません。 今後PowerQueryがVBAで頻繁に使われるようになった時には、解説できるようにしたいと思っています。 上記では、マクロの記録で作成されたものを一部変更しただけのものになります。 主な変更点は、 ・Chr(13) & "" & Chr(10) 、これは改行なので不要 ・クエリ名をVBAで扱いやすいように変更 改行は消す必要はないのですが、 ここでは掲載した時のゴチャゴチャ感を減らすために消したまでです。 その他の日本語が使われている部分も短くしたいところですが、マクロ記録と比較しやすいようにそのままにしてあります。
QueryTableのプロパティは省略できるものも多いのですが、 後々の変更を考えれば、そのまま残しておいた方が良いと思います。 PowerQueryについてPowerQueryは、名前の通りQueryの機能を強化したものです。 基本部分は、以前からあったQueryTable（テキストデータのインポート等）を強化したものと考えれば良いでしょう。 M言語を導入しエディタからM言語を自動生成されるようになっていて、この機能がとても優れていると思います。 特にテーブル機能と上手く連携していて、かなりスムーズに扱えるようになっていると感じます。
DB（データベース）を良く扱う人にとっては、そんなに理解しづらいものではないと思います。 ADOを使いSQL発行するのと同じ考え方になります。・データベースの種類 ・SQL（SQL:Structured Query Language） ・ADOを使う準備 ・ADOでのDB接続方法 ・ADODB.Recordsetの取得方法 ・ADODBのレコードセットの扱い方 ・ADODBのトランザクション処理 ・ADODB.Commandの使い方 ・VBA100本ノックでの実践例 ・最後に注意点等
つまり、M言語を覚えるのはSQLを覚えることと似ている部分は多いでしょう。 ただし、いずれにしても一つの言語になりますので、学習には相応の時間がかかるとは思います。 M言語を直接スラスラ書けるに越したことはありませんが、 PowerQuryエディタの自動生成を上手く利用していくことでVBAでの活用範囲は広がるはずです。ただし、やはり自動作成されたM言語は無駄な部分もかなり多いように見受けられました。 これは、マクロの記録で作成されたVBAと同じだろうと思います。 今後、WEBや書籍でクエリの具体例と解説が増えてくるはずですので、それを基に作成するという使い方もできるようになってくるでしょう。
同じテーマ「マクロVBA技術解説」の記事 新着記事 NEW ・・・新着記事一覧を見る アクセスランキング ・・・ ランキング一覧を見る このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。 掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。 本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。 This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.