【VBAリファレンス】Excelファイル内の全Power Query（M言語）を一括抽出する：VBAによるソースコード管理の自動化

概要

ExcelのPower Queryは、複雑なデータ加工プロセスをGUIで構築できる強力なツールですが、その中核である「M言語（Power Query M Formula Language）」は、Excelの各クエリの裏側に隠蔽されています。通常、クエリのソースコードを確認するには、Power Queryエディターを一つずつ開き、「詳細エディター」を確認しなければなりません。

もし、数百のクエリを持つブックの全ロジックをドキュメント化したい場合、あるいは特定の処理を一括検索したい場合、手作業では膨大な時間がかかります。本記事では、Excelブック内に格納されたすべてのPower QueryのM言語スクリプトを、VBAを使用して一括でテキストファイルとして抽出する技術を解説します。この手法を習得することで、クエリのバージョン管理、監査、およびドキュメント化の効率が劇的に向上します。

詳細解説

Excelのブック内にあるPower Queryの定義は、実はブック内部のXMLストリームとして保存されています。VBAからこれにアクセスするには、主に「Workbook.Queries」コレクションを使用します。

このコレクションは、ブック内に定義されたすべてのクエリを保持しています。各クエリオブジェクトには「Formula」というプロパティがあり、ここにM言語のソースコードが格納されています。このプロパティをループ処理で取得し、FileSystemObject（FSO）を用いて外部テキストファイルに書き出すことで、全クエリのバックアップや検索可能なデータベースを作成することが可能です。

注意点として、Power Queryには「クエリ」と「接続」の概念が含まれますが、VBAのQueriesコレクションを使えば、個々のクエリの定義式を正確に抜き出すことができます。また、抽出したテキストはUTF-8で保存するのが定石です。M言語には特殊記号が含まれる場合が多いため、文字コードの指定を怠るとコードが破損するリスクがあるためです。

サンプルコード

以下のVBAコードは、実行するとダイアログが表示され、指定したフォルダにブック内の全クエリのソースコードを個別のテキストファイルとして出力します。


Option Explicit

Sub ExportAllPowerQueries()
    ' 必要な参照設定: Microsoft Scripting Runtime
    Dim wb As Workbook
    Dim q As WorkbookQuery
    Dim fso As Object
    Dim ts As Object
    Dim folderPath As String
    Dim fDialog As FileDialog
    
    Set wb = ThisWorkbook
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 保存先フォルダの選択
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If fDialog.Show = -1 Then
        folderPath = fDialog.SelectedItems(1) & "\"
    Else
        Exit Sub
    End If
    
    ' 全クエリをループ処理
    For Each q In wb.Queries
        ' ファイル名として使用できない文字を除去する処理が必要な場合がある
        Dim fileName As String
        fileName = folderPath & q.Name & ".txt"
        
        ' UTF-8で出力するためにADODB.Streamを使用する例
        Dim adoStream As Object
        Set adoStream = CreateObject("ADODB.Stream")
        
        With adoStream
            .Type = 2 ' adTypeText
            .Charset = "UTF-8"
            .Open
            .WriteText q.Formula
            .SaveToFile fileName, 2 ' adSaveCreateOverWrite
            .Close
        End With
        
        Debug.Print "Exported: " & q.Name
    Next q
    
    MsgBox "全クエリの抽出が完了しました。", vbInformation
End Sub

実務アドバイス

実務においてこのコードを運用する際のポイントをいくつか挙げます。

まず第一に、「コードの可読性向上」です。抽出したM言語は、Power Queryエディター上で整形されていない状態で保存されていることが多いです。抽出後にPythonなどのスクリプトを用いてインデントを整えることで、レビューが非常に楽になります。

第二に、「バージョン管理」との連携です。抽出したテキストファイルをGitなどのバージョン管理システムで管理することで、クエリの変更履歴を追跡できるようになります。これは、大規模なデータモデルを扱う組織において、属人化を防ぐための極めて重要なプラクティスです。

第三に、「クエリのグループ化」への対応です。大規模なブックではクエリがグループ分けされていることがありますが、Queriesコレクションではフラットに抽出されます。抽出したファイル名にグループ情報を含めたい場合は、内部のXML（`xl/queries.xml`）を解析する必要があります。これはより高度な技術ですが、`Zip`としてブックを開き、中のXMLを直接読み込むアプローチで実現可能です。

最後に、セキュリティ上の注意です。Power Query内には、データベースの接続文字列や、場合によってはハードコーディングされたAPIキーが含まれている可能性があります。これらをテキストとして抽出・保存する際は、アクセス権限を厳重に管理し、機密情報が含まれていないかを確認するプロセスを組み込んでください。

まとめ

Power QueryはExcelのデータ処理を近代化しましたが、そのソースコードがブック内に閉ざされていることは、管理上のブラックボックス化を招きがちです。今回紹介したVBAによる一括抽出手法は、単なるバックアップにとどまらず、ドキュメントの自動生成、変更履歴の可視化、そしてチーム開発におけるナレッジ共有の強力な武器となります。

VBAは、こうした「GUIでは手の届かない場所」にアクセスするための鍵です。ぜひ本コードをベースに、皆様の業務環境に合わせてカスタマイズしてください。コードを自動で抽出・管理する習慣をつけることで、データ分析プロセスの信頼性と保守性は飛躍的に向上することでしょう。