【Window業務効率化】バッチファイルとGetObjFmXlsmでExcelのPowerQuery M式言語クエリを抽出しよう

バッチファイルとGetObjFmXlsmでExcelのPowerQuery M式言語クエリを抽出しよう

この記事では

バッチファイル「GetObjFmXlsm.bat」
ユーティリティプログラム「GetObjFmXlsm.exe」

を利用してExcelファイル内のVBAコード、 PowerQuery M式言語クエリを抽出する方法について、詳しく紹介します。

これにより、通常のExcelファイル（.xlsx）やExcelマクロファイル（.xlsm）から、VBAコード、PowerQuery M式言語クエリをテキスト形式で抽出することが可能です。

これらのツールはVBAコードだけでなく、PowerQuery M式言語クエリの内容も抽出できるため、SubversionのクライアントTortoiseSVNと連携して、VBAコードおよびPowerQuery M言語クエリを一括して差分管理することができます。

【Window業務効率化】バッチファイルとGetObjFmXlsmでExcelのPowerQuery M式言語クエリを抽出しよう — **しらかば堂**

こんにちわ。しらかば堂です。
この記事が少しでも皆さんのお役に立てれば幸いです。

PowerQuery Ｍ式言語クエリ

まずExcelの PowerQuery M式言語クエリ についてあまり詳しくご存じない方のために、少しだけPowerQuery M式言語クエリ とは何かについてご紹介しておきます｡既にご存じの方はこの段落をスキップしてください。

Power Query M式言語クエリは、Excel 2016以降のバージョンで、Excelの標準機能として組み込まれた、データ収集、整形をM式言語クエリとして簡単に行うためのしくみです。

そしてこのクエリは自動記録や追加ができるので、一度クエリを作ると同じデータを更新する際に何度でも再利用できます。

また、新しいデータが追加された場合にも、同じクエリを適用して更新可能です。

但しここで使用しているM式言語クエリはAccess、SQL Server といった製品で使用されているSQL言語をベースとしたクエリとは全く別なものなので注意が必要です。

この記事中では簡単のために PowerQuery Ｍ式クエリを単にクエリと表現します。

項番	特徴	説明
1	データ収集	さまざまな外部データソース（例えば、Excelファイル、CSVファイル、データベース、Webページなど）からデータを簡単に収集できます。
2	データの整形	複数のステップをデータを変換し、望ましいテーブル形式に整形できます。例えば、列を追加、データの並び替え、フィルタリング、計算、データ同士の結合などを簡単に行えます。

PowerQuery公式情報

この PowerQuery Ｍ式言語クエリについて詳しく知りたい方は Microsoft の公式資料 Power Query M 式言語 あるいは以下の書籍で確認下さいね。

リンク

利用方法

このバッチファイル、ユーティリティプログラムを

％USERPROFILE%\Tools\bat\GetObjFmXlsm.bat
％USERPROFILE%\Tools\exe\GetObjFmXlsm.exe

配下に配置し、さらにこの GetObjFmXlsm.bat のコピーを､例えば以下のようなVBAコード

および PowerQuery M式言語クエリ rakkokeyword_2023429174817

を含むターゲットファイル

比較.xlsm

と同じフォルダ配下に配置して､このバッチファイルを起動すると

指定のフォルダ内にあるすべてのモジュール（標準モジュール、Sheetモジュール、ThisWorkbookモジュール）に含まれたVBAコードとクエリを、Shift JIS（SJIS）文字コードのテキスト形式で抽出して保存します。

保存されるファイルはそれぞれ、モジュール名.bas（VBAコード用）とクエリ名.pq（クエリ用）という名前で保存されます。

この GetObjFmXlsm.bat のコピーをターゲットとなるVBAコード、あるいはクエリを含む*.xlsmと同じフォルダ配下に配置する理由は、このバッチファイルのダブルクリックでファイル名を指定せずにVBAコード、クエリを抽出する利便性を確保するためです。

また ExportVBAcodeFromXlsm.bat は関連記事で使用したVBAコード抽出に特化したツールですが､今回は使用しません｡

【Window業務効率化】バッチファイルとGetObjFmXlsmでExcelのVBAコードを抽出しよう

この抽出された Ｍodule1.bas は以下のように先ほどのVBEで確認したものと同一です。

Module Module1
Option Explicit

Sub 比較コピー1()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim wb3 As Workbook
    Dim ws1 As Worksheet
    Set wb1 = Workbooks.Open(GetFoldersFilesNamewithDialog())    '前回ファイル
    Set wb2 = Workbooks.Open(GetFoldersFilesNamewithDialog())    '最新ファイル
    Set wb3 = Workbooks.Open(GetFoldersFilesNamewithDialog())    'マスターファイル

    
    '　Set ws1 = Worksheets("データ")   ' 書き込み用ファイルシート　ここで型が一致しませんとデラーが出る
    
    Windows("比較.xlsm").Activate
    ActiveSheet.Range("A:C").Clear

   
'    Dim r1 As Range
'    Set r1 = wb1.Worksheets("もも1").Range("a7").CurrentRegion    '前回ファイル

    Dim r2 As Range
    Set r2 = wb2.Worksheets("もも1").Range("a7").CurrentRegion    '最新ファイル
    
    Dim r3 As Range
           :

また抽出された rakkokeyword_2023429174817.pq も以下の通り先ほどの PowerQuery 詳細エディタで確認したものと同一です｡

let
    ソース = Csv.Document(File.Contents("C:\Users\devel.RVH\Downloads\rakkokeyword_2023429174817.csv"),[Delimiter="	", Columns=4, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"No", Int64.Type}, {"階層", Int64.Type}, {"単語数", Int64.Type}, {"キーワード", type text}}),
    削除された列 = Table.RemoveColumns(変更された型,{"階層"})
in
    削除された列

ここでサンプル表示している比較.xlsm というマクロファイルは知り合いから入手した練習用のVBAコードなので､その内容にあまり意味はありません｡また、

同一フォルダ内に含まれている複数 xlsm ファイル中に同一モジュール名が含まれていると、先に抽出された bas ファイルに上書きされる
ツール起動後直ぐに実行が開始されるような xlsm ファイル、すなわち ThisWorkbook モジュール内に Wookbook_Open() が含まれている場合、これを Wookbook_Open_() 等に名称変更して自動起動しないようにしないと､正常にVBAコード抽出できない

ので注意が必要です｡

解決できること

これらのツールと Subversion クライアント TortoiseSVN を一緒に利用すると

VBA､あるいはクエリの含まれた .xlsm ファイルの VBA､あるいはクエリに修正をかける
GetObjFmXlsm.bat を起動し､VBAコード､あるいはクエリを抽出する
TortoiseSVN で上記の修正に対する差分を抽出する
上記の差分も含めた全てをTortoiseSVN を使ってリポジトリにコミットする

といった一連の作業によリ､VBAコード､あるいはクエリの差分管理が簡単にできるようになります。

そしてこのSubversion が Redmine と連携していれば､さらに問題(チケット)管理と修正したVBAの紐づけができるようになり、個人、チームでのVBA、クエリ開発の変更の可視化、維持管理に計り知れないメリットを提供します｡

【Windows 環境構築】共同作業がスムーズになる！自宅、会社のワークスペースで Subversion を使ってみよう

【Windows環境構築】Subversion のクライアント TortoiseSVN 環境を整え開発効率を爆上げしよう

【Windows環境構築】RedmineとSubversionの連携方法とメリットを徹底解説！プロジェクト管理と品質管理を効果的に遂行する方法

読んでほしい方

Excelで VBA、クエリ等を使った業務効率化に取り組まれている方
Subversion を使った開発リソースの差分管理を実施中、もしくは検討されている方
Excel VBA マクロ、クエリの維持管理の継承性を考慮している方､または苦労されている方

バッチファイルの使用方法

それでは本ツールの具体的な利用イメージが連想できるように､題材としている 比較.xlsm に実際にクエリを追加するところを含めて､手順をご紹介しましょう。

クエリの追加

既に対象となるクエリをお持ちの方はこのクエリ追加部分を読み飛ばし､あるいはご自身のクエリで読み替えて頂けると幸いです。

最初にフォルダの中から対象となる 比較.xlsm をオープンし

Excel のリボンメニューから

データ > クエリと接続

を順にクリックして PowerQuery モードにしてから

データの取得 ＞ ファイルから(F) ＞ テキストまたはCSVから(T)

を順にクリックし

開いたデータの取り込み画面から取り込みたい csv ファイルを選択後、インポート(M)ボタンをクリックします。

ここで選ぶデータは自分が取り込みたいものをデータの取得メニューをたどって選びます。

このデータとしてはさまざまな外部データソース（例えば、Excelファイル、CSVファイル、データベース、Webページなど）が選択可能ですが

この記事中では利用Windows PC内のダウンロードフォルダに格納されていた
キーワードリサーチツール「ラッコキーワード」
からのダウンロード csvファイル rakkokeyword_2023429174817.csv をサンプルとして使用しました。

そしてcsvのインポートが終わると､以下の画面になるので データの変換 ボタンをクリックします。

すると以下のような PowerQuery エディターが現れるので、一旦(PowerQuery エディター画面を) 閉じて読み込む ボタンをクリックします｡

すると次の画面が現れ

すると次の画面が現れ､PowerQuery エディターの中で指定した rakkokeyword_2023429174817.csv ファイルがテーブル形式でExcelの中に読み込まれ、この一連の動作が rakkokeyword_2023429174817 クエリとして記録されます｡

ここでPowerQuery モードの中で右側の クエリと接続 領域にある先ほどのrakkokeyword_2023429174817 クエリをクリック後、続けて画面下の編集ボタンをクリックすると

以下のPowerQueryエディターー画面になるので、左上の 詳細エディタ― ボタンをクリックして

現れる以下の画面が今回ツールで抽出を行うクエリになります。

では､完了ボタンをクリック後

左上の閉じて読み込むボタンをクリックして､PowerQuery モードを終了しましょう。

そしてこの 比較.xlsm 自身も閉じるとフォルダ内は 比較.xlsm に変更が加わり、Subversion のリポジトリ間に差分が発生したので、以下のように赤い！マークのアイコンに変わっています。

GetObjFmXlsm.bat 起動

ではここでこのフォルダに前もってコピーしておいた GetObjFmXlsm.bat をダブルクリックして起動してみましょう｡

するとターミナル画面上は以下のようになり


GetObjFmXlsm.exe　: ver 1.191 (2023/07/30 11:26:05)

使用方法:

>GetObjFmXlsm /h                    ... このヘルプを出力します

>GetObjFmXlsm /v sample.xlsm        ... sample.xlsm ファイルからVBAコードを抽出し、モジュール名.bas を出力します

>GetObjFmXlsm /m sample.[xlsx|xlsm] ... sample.xlsx または sample.xlsm ファイル内からM言語を抽出し、クエリ名.pq を出力します
続行するには何かキーを押してください . . .

ここでリターンキーを叩くと先ほどのフォルダは以下のようになります｡
赤線で囲んだ２つのファイルが増えましたね。

この2つのファイルの内容を確認すると

let
    ソース = Csv.Document(File.Contents("C:\Users\devel.RVH\Downloads\rakkokeyword_2023429174817.csv"),[Delimiter="	", Columns=4, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"No", Int64.Type}, {"階層", Int64.Type}, {"単語数", Int64.Type}, {"キーワード", type text}})
in
    変更された型

および

Module Sheet3
Option Explicit

End Module

となっています。

この Sheet3.bas というのはクエリが新規に定義されたシート内に新たに定義されたシートモジュールで、今回は特にVBAコードを追加していないので､デフォルトで自動設定される

Option Explicit

定義のみが保存されています。

追加されたファイルのリポジトリへの追加

では今回新規に増えた2つのファイルをリポジトリに追加しましょう｡

まず比較.xlsm 自身が保存されているフォルダ xlsm を選択し､マウスの右ボタンのコンテキストメニューから

TortoiseSVN ＞ ＋追加(A)…

を順にクリックします。

そして OK ボタンクリックして

この2つのファイルを Subversion のリポジトリへの管理対象として追加してから

追加されたファイルのリポジトリへのコミット

続いて同様に

TortoiseSVN ＞ SVNコミット(C)…

を順にクリックし

現れた TortoiseSVN のコミット画面にて画面中央赤下線でハイライトしたようなコメントを入力し、OK ボタンをクリックすると

再度以下のような確認画面が現れるので、再度 OK ボタンをクリックします。

すると比較.xlsm 格納フォルダ は以下のようになり、全てのファイルが緑✅アイコンの付いたリポジトリとの差分がない状態になりました。

クエリの変更

さてここからは少し手順を省略しながら先ほどの rakkokeyword_2023429174817 クエリから”階層“という列を削除、保存してみましょう。

具体的には先ほどと同様の手順で PowerQuery エディターを呼び出し、テーブル上の赤線で囲まれた “階層” 列をクリックして選択後に、左上の 列の削除 ボタンをクリックします。

そしてこのクエリの変化を確認するために先ほどと同様に PowerQuery 詳細エディタ― ボタンをクリックすると

詳細エディタの中で以下の赤線で囲まれた部分が追加、変更されています。

具体的には以下のように

let
    ソース = Csv.Document(File.Contents("C:\Users\devel.RVH\Downloads\rakkokeyword_2023429174817.csv"),[Delimiter="	", Columns=4, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"No", Int64.Type}, {"階層", Int64.Type}, {"単語数", Int64.Type}, {"キーワード", type text}}),
    削除された列 = Table.RemoveColumns(変更された型,{"階層"})
in
    削除された列

なりました。

そして先ほどと同様に PowerQueryモードを終了し、比較.xlsm 自身も閉じ、そして GetObjFmXlsm.bat をダブルクリックして起動した結果として 比較.xlsm 格納フォルダは以下のようになります。

ここでは比較.xlsm 内のPowerQuery Ｍ言語においての”階層”列を削除して保存後、この GetObjFmXlsm.bat を実行しているのですが、 TortoiseSVNの機能により変更のあったファイルのアイコンが赤い！に変わります。

クエリの差分確認

ここでリポジトリとの差分が発生した rakkokeyword_2023429174817.pq をマウスで選択し、右クリックからのコンテキストメニューで

TortoiseSVN ＞ 差分を表示(D)

を選択すると

以下のようにリポジトリとの間の差分が表示されます。

4-5行目でピンポイントで修正前後のPowerQueryのM式コードの差分が見え、テーブルから”階層”列が削除されていることがわかりますね｡凄くないですか?

GetObjFmXlsm.bat の紹介

バッチファイルの保存場所

本記事ではこのバッチファイルを以下の記事も参考にしながら

【Windows業務効率化】バッチファイルでツールのフォルダ環境を自動生成しよう

％USERPROFILE%\Tools\bat\GetObjFmXlsm.bat

に保存し、そのコピーをVBAコードを抽出したい .xlsm ファイルが格納されているフォルダに保存します。%USERPROFILE% については必要に応じて以下の記事も参照ください。

関連記事【Windows業務効率化】バッチファイルの基本テクニックを知ろう

バッチファイルの詳細

このバッチファイル GetObjFmXlsm.bat は、Excelマクロ有効ブック(.xlsmファイル)内のVBAコードをエクスポートするためのバッチファイルで、カレントディレクトリ内のすべての.xlsmファイルに対し、そのファイル名をパラメータとしてGetObjFmXlsm.bat を呼び出します。

@echo off
setlocal

%USERPROFILE%\Tools\exe\GetObjFmXlsm.exe /h

taskkill /f /im EXCEL.EXE >nul 2>&1

for %%f in (*.xlsx) do (
	 %USERPROFILE%\Tools\exe\GetObjFmXlsm.exe /m "%%~ff"
)

for %%f in (*.xlsm) do (
	 %USERPROFILE%\Tools\exe\GetObjFmXlsm.exe /v "%%~ff"
	 %USERPROFILE%\Tools\exe\GetObjFmXlsm.exe /m "%%~ff"
)

pause
endlocal

GetObjFmXlsm.batの簡単な説明

行番号	説明
1	このバッチファイルで echo コマンドを除いた以後の実行コマンドそのもの、あるいはその実行結果を以下のようにコマンドプロンプト上に表示しないように指定
2	一時的な環境変数を作成し、スクリプト内での変更を限定します。
4	GetObjFmXlsm の評価版/有償版識別のためのヘルプ情報を出力します。
6	現在実行中の EXCEL.EXEという名前のプロセスを念のため強制終了します。エラーメッセージは表示せず、結果を無視します。
8-10	カレントディレクトリ内のすべての.xlsxファイルをパラメータとして %USERPROFILE%\Tools\exe\GetObjFmXlsm.exe /m つまりクエリ抽出機能を繰り返し呼び出します。
12-15	カレントディレクトリ内のすべての.xlsmファイルをパラメータとして %USERPROFILE%\Tools\exe\GetObjFmXlsm.exe /v つまりVBA抽出機能 %USERPROFILE%\Tools\exe\GetObjFmXlsm.exe /m つまりクエリ抽出機能を繰り返し呼び出します。
17	GetObjFmXlsm の評価版/有償版識別のためのヘルプ情報を確実に参照できるよう、ここでいったんポーズします。必要に応じて削除してください。
18	setlocalで作成した一時的な環境変数を破棄し、元の状態に戻します。

GetObjFmXlsm.exe の紹介

保存場所

バッチファイルと同様に

%USERPROFILE%\Tools\exe\GetObjFmXlsm.exe

に保存します。

入手方法

この GetObjFmXlsm.exeファイルはしらかば堂が開発したユーティリティプログラムで以下の2種類のインストーラを準備しており、インストーラを使って上記のフォルダに GetObjFmXlsm.exe として保存可能です｡

まず無償の評価版でその動作､効果を体感して頂き､気に入って頂けた方は有償版の購入をご検討くださいね｡

		評価版	有償版
説明	インストーラ入手先	ダウンロード
	価格	無償	2,000円
	利用可能期間	1カ月	無制限
機能	ヘルプ^(注2)	〇	〇
	.xlsm からの .bas 抽出^(注3)	〇	〇
	.xlsx または .xlsm からの .pq 抽出^(注4)	〇	〇

(注2) ヘルプ … この GetObjFmXlsmコマンドで利用可能なコマンドスイッチ、使い方を表示します。

(注3) .xlsmからの .bas 抽出 … 例えば Sample.xlsmの中に VBA コードを含むフォームモジュール、標準モジュールが定義されているとき、/v スイッチを使用してこれらのモジュールからVBAコードを モジュール名.bas として抽出します｡

(注4) .xlsx または .xlsm からの .pq 抽出 … 例えば Sample.xlsxの中にPowerQuery のM言語によるクエリ定義が存在する場合､/m スイッチを使用してこれらのクエリをクエリ名.pq として抽出します｡

起動スイッチ

Windows 10 のコマンドプロンプト配下でこの GetObjFmXlsm.exe のヘルプ機能を実行すると以下のようになります。

>GetObjFmXlsm /h                    ... このヘルプを出力します

>GetObjFmXlsm /v sample.xlsm        ... sample.xlsm ファイルからVBAコードを抽出し、モジュール名.bas を出力します

>GetObjFmXlsm /m sample.[xlsx|xlsm] ... sample.xlsx または sample.xlsm ファイル内からM言語を抽出し、クエリ名.pq を出力します

>