Excel自動化ならばPythonよりもVBSを使いましょう

世にRPAが出てからというもの、Power Automate for DesktopやPythonでExcel自動化なんて記事が出回るようになりました。前者はシステム全体の自動化につながるものであるため、個人的には推奨しています（年間何百万もするRPAなど愚の骨頂です）。

しかし、PythonでExcel自動化は推奨しません。そもそも、Excelの自動化は以前よりVBAやVBSで出来ている事。それ以外も含めてならば話は別ですが、世に出てる記事の殆どは「VBSで出来ることをわざわざPythonでやらせてる」という無駄と無用な手段に他なりません。ちなみにVBSはシステムの操作も可能であるため、Seleniumでスクレイピングでもしない限りほぼ、現状PythonでExcel自動化は非推奨です。

今回はVBSでのExcel自動化の一片を記述してみたいと思います。

概要

Pythonで自動化の愚

まず、よくあるExcelのPytyonでの自動化が何故非推奨なのか？という事ですが、まずもって自動化の必要なシーンの多くは事務作業です。そのデスクワーク業務（経理や人事・給与計算等）に於いて、何故愚行であるのか？それを列挙してみようと思います。

1. そもそも通常の企業では、事務方のPCへのPythonインストールは許可しない（VBSがあるのだから不要と判断）。
2. 圧倒的な知見の多いVBA、VBSと比較して、PythonでのExcel操作は知見がそこまで十分とは言えない。
3. Excelを操作出来ると言っても、残念ながら単体で出来るわけではなく、外部ライブラリに依存しています（これらの永続性等に影響される）
4. 3.の個別のライブラリによって、記述内容が大きく変わるため、メンテナンス上の問題が生じる
5. 基本的にPythonとライブラリによって出来る事は、読み書きと一部メソッドの実行だけ。実際に細かい要求に応えられていない。
6. macOSでも使えるという利点をあげるサイトがありますが、ほぼ事務の現場でmacOSが採用されることは有りません。
7. 処理速度がPythonのほうが高速とありますが、その速度を求めるだけのデータ量は事務の現場にはほぼ存在しません（そういった処理は基幹業務システムが担ってる）
8. 何よりもクライアント毎にPython実行環境の構築が要求される（EXE化して配布は可能ですが、そういったタスクをつくり手に要求されます）

Pythonの学習であったり、極めて大量の統計データの処理、基幹業務システムとの連携等が必要とするようなシーンがあるならともかく、一般的な事務の現場では、ほぼ以上の理由により、現場にPythonを導入して自動化しましょう、とはなりません。VBSは現在のWindowsには元から標準装備されているもので、環境構築など不要です。

VBAで他のアプリケーションを操作する

基本的な知見はVBAと変わらない

VBSに関する知見はほとんどVBAと変わりません。VBAと異なる点としては

1. ExcelやWordなどの実行するための土台を必要としておらず、単体で実行可能（Officeの入っていないマシンでも実行可能）
2. コマンドラインから引数を渡して実行させる事が可能（情シスが起動時の自動処理でよく利用してる）
3. Excel VBAと同じメソッドやコマンドを利用する事が可能である
4. SAP GUI Scripting等の自動化機能でも利用されている

その為、コードの書き方についても殆ど同様に記述が可能であり、VBAを学んでいるものならば、既にもう使いこなせるものです。これを無視してPythonという話にはならないわけです。そしてそのケースはほぼアリません。

SAP GUI Scriptingで自動操縦してみた

実行時の注意点

VBSファイル実行時の注意点ですが、vbsファイルの文字コードがUTF8の場合、中に書かれてる日本語が文字化けし、例えばそれがフルパスなどの場合だった時、正しいパスとして認識されずエラーになります。そのため、VBSファイルを作成する場合は基本的には、Shift-JISの形式で保存するようにしましょう。メモ帳で文字コードを変換して保存する場合の手順は以下の通りです。

1. メモ帳を開いて、対象のVBSファイルを読み込ませる
2. 名前をつけて保存を実行
3. 文字コードをANSIに変更して保存する

これで、文字化けせずに実行する事が可能です。

図：ANSIに変更しておくのが定石

タスクスケジューラで定期自動実行

VBAの入ったExcelファイルを実行出来なくも無いのですが、引数渡し等を考えると無理やりそのために仕込むよりも、VBSで開くほうが引数も使える為、素直に実行できます。そして、このVBSを定期的に自動実行したいという要望は結構あります。RPA等ではこういった機能が有償で提供されていますが、VBSの場合、これまた標準装備されてる「タスクスケジューラ」で、細かな条件で定期的に自動実行させる事が可能です。

1. 実行させるVBSファイルへのフルパスを取得しておく
2. コントロールパネル⇒システムとセキュリティ⇒Windowsツールの中にタスクスケジューラがあるので開く（コマンドラインならTaskschd.mscで起動できる）
3. 右パネルの「タスクの作成」をクリックする
4. 名前を適当に決めて、セキュリティオプションを指定（ログオンしていなくても実行可能）
5. トリガー⇒新規作成を開き、時刻や実行間隔（1日起きなど）やタスクの開始イベントの種類を指定する
6. 操作にて、プログラムスクリプトの欄にVBSのフルパス、開始はそのVBSがあるフォルダ（カレントディレクトリ）、引数の追加にはcscript.exeを指定します。
7. 更にトリガー以外にも実行条件を加えられます。
8. 最後にOKを押すと、これでスケジュールが登録されて、指定条件の指定トリガー時に自動的に発火するようになります。

VBSおよびタスクスケジューラという昔から標準装備されていて、誰でも使える環境を使わずして、自動化を語ることなかれです。

図：昔から使われてきたテクニックです

知ってると便利なVBSでのExcel操作

コマンドライン引数

同じような処理をするけれど、対象とするExcelは変動するようなケースや、その場合に使う値等が動的な値の場合には、VBS側で入力用のinputboxなどを用意する必要があります。しかし、これは人間が単体実行で完了するようなケースでは良いですが、自動化を施す場合いちいちinputboxから入力はスマートではありません。

そこで、VBSを実行時に、必要なExcelファイルへのフルパスやファイル名などを渡して、それに基づいて処理をする為の仕組みがコマンドライン引数です。この場合、VBSファイルをダブルクリックで実行ではなく、以下のような形で実行します。

nologoはオプションで、「画面にMicrosoftのロゴを表示しない」為のオプション。出力結果をテキストなどに出力する場合、この文字まで出力されてしまうので、それを防ぐ為に利用します。複数の引数を渡す事が可能で、その場合、VBSの冒頭に以下のような形で引数を分解して取得する必要があります。

こうする事で、上記の1つ目の引数と2つ目の引数を取得⇒分解し、変数に格納する事が可能になります。1つしか引数が無い場合にはargsの時点で終了です。

特定のブックだけを閉じる

VBSの自動操縦中に他のExcelで作業を続けたいというケースは事務の現場では多いです。特に１個の処理が割りと長い場合、お茶飲んで待つというわけには行かないので、この要望は無視出来ません。しかし、処理が終了したExcelを閉じる場合に、Excelのプロセスを閉じてしまうと、並行作業中のExcelまで終了してしまうので、非常によろしくありません。そこで、以下のように特定のブックだけを終了させるコードをVBSの最後のほうに記述をしておきます（Excel自体は終了していないので開いてるブックが0でもプロセスは残ります）

起動中のExcelをGetObjectで取得し、ファイル名でサーチしてヒットしたものをCloseしてる処理になります。

特定のシートの領域をテーブル化する

自動化時代到来の現世に於いて、Excelを事務の現場で使う場合、テーブル化して使うのはもはや当たり前です。また、VBAやVBSからデータを扱う場合もテーブル用のメソッドを使ったほうが遥かに楽なので、基幹業務システムから落としてきたデータなどを一括してテーブル化する必要性が出てきます（これを毎回人力でやるのは生産的ではない）。

以下のようなコードで、Excelのファイルを開いて特定のシート全体を１枚のテーブルにしてしまいます。

Workbooks.OpenにExcelファイルへのフルパスを渡して、UsedRangeで利用可能なデータ領域全体を、最後にLisgObject.Addにてテーブル化します。テーブル名は適当なものを指定しましょう。

Excelではテーブルとクエリを活用すると楽になる

ワークブックを保存して終了する

VBAではおなじみの、オブジェクトを利用したら最後は閉じます。以下のおまじないはこの手の処理をした場合には必ず必要になりますので、追記しておきましょう。以下のコードは前述のコードの続きとして記述します。Saveにて保存します（保存ダイアログ等は出ず、上書き保存となります）

最後にオブジェクトに対してNothingを代入する事で、使用するメモリ領域を開放します。

返り値を返す

コマンドラインからの実行などで、実行結果を返したい場合、コンソールに出力が必要です。以下のようなコードをコードの最後に記述して、出力する事で、実行した側のアプリケーションで返り値として受け取る事が可能です。

但し注意したいのが、コードの途中でコンソール出力をしてしまうと、相手は返り値が返ってきたと考え、その内容を取得してしまうので、基本コードの最後に１度だけ利用します。

処理をスリープさせる

VBS自体は、VBA同様に手続き型言語であるため、同期的に処理が進むので、Node.jsみたいに前の処理が終わっていないのに、次の処理に進んでしまうといった事がありません。必ず関数を使っても返り値を待ってから次に進みます。

しかし、Excel外のシステムとの連携の場合、そのシステムからのreturnが無いケースではある程度待ってから処理といったテクニックが必要なケースはままあります。この場合以下のようにウェイトを入れるコードを使います。

上記のコードで、5000ms（5秒）のウェイトを入れています。

テーブルデータを削除する

Excelファイルの自動処理に於いて、他のデータをもって洗い替えで入れ替えたい場合、テーブル化しておくのが定石です。そうすることで、いちいちデータの最終行を把握しておく必要がありません。そのデータを入れる前にはまず、テーブル内のデータを全削除する必要がありますが、以下のようなコードで対象のテーブルデータを空っぽにする事が可能です。

上記のコードで指定のファイルの指定のテーブルに接続し、中身をDelete一発です。その列数やレコード数を把握しておく必要などアリません。これが、素の状態で使わずテーブル化しておくメリットです。

テーブル間でデータをコピーする

最新データというテーブルと、更新前というテーブルの間でデータの比較をしたい場合、最新データを入れる前に、まずは最新データを更新前テーブル側にコピーが必要です（合わせて、前述のテーブルデータの削除も必要）。

この場合、2つのテーブルに接続し、以下のようなコードでデータをごっそりコピー可能です。前述の内容同様に列数やレコード数を把握しておく必要は有りません。

最新情報シートのmaindataテーブルの内容をDataBodyRange.Copyで取得し、書き込みポイントの左上である更新前のA3に貼り付けている処理です。更新前テーブルにはこれで新しくデータがテーブルに挿入されます。

Power Queryのテーブルを更新する

外部データを自動処理するのに、もはやPower Queryを使うのは現代では常識です。いちいち複雑な関数やらピボットテーブル等を駆使するのではなく、Power Queryで処理をしておく事で、ほぼインポートからデータのフィルタ、結合などの処理は自動化出来ます。

この結果出力されたデータもまたテーブルなのですが、「右クリックして更新」をする事で最新データを取得し処理をした結果が表示されます。この処理をVBS実行時のタイミングで行わせたい場合は以下のようなコードを書くだけでOKです。但し、事前にクエリに対して以下のオプション設定をしておく必要があります。オプション設定をしていない場合、クエリの更新が終わる前に次の処理に進んでしまいます。

1. 仕込んであるExcelを起動して、データ⇒クエリと既存の接続を開く
2. 右サイドパネルに対象のテーブルのクエリ一覧が出てくるので、対象のクエリを右クリック⇒プロパティを開く
3. コントロールの更新の「バックグラウンドで更新」のチェックを外す（デフォルトでオンになってる）
4. OKを押して保存する

ファイルを開くときに更新すれば良いじゃないかと言う人もいますが、順番にクエリを実行したい場合などにはこれではNGです。故にコードから順次処理をしたい場合にはこの処理が必要です。また、開いたら常に最新に勝手にされても困るケースにも有効です。

そして、この処置をしたテーブルを以下のコードで更新を実行する事が可能です。

これで任意のタイミングで対象のテーブルを更新する事が可能になりました。また、テーブルの更新が終わるまで、Refresh以下のコードは実行されずに待機してくれるので、この組み合わせは覚えておきましょう。

図：バックグラウンド更新させない

Excelで身に付けるべきスキルコース（松）

テーブルを拡張してからデータを入れる

テーブルに対してデータの塊をガッツリ入れる場合、前述のコピペのようにテーブルのデータ領域左上の起点に対して、貼り付けるだけでOKなのですが、非常に大量のデータがある場合には、このままの手法だとかなり処理が遅くなります。

そこで、事前にコピペ前のテーブルデータの量 + 500といった余分に拡張しておく事で、貼り付け速度の遅さを低減する事が可能です。その為にはテーブルを拡張する必要があります。

まず、ListRows.Countでテーブル内のレコード量を把握します。これに対して、+500分だけテーブルを拡張しておきます。

実際に貼り付けるデータがこのトータルよりも少ない場合であっても、メソッド実行後は自動的にテーブルレコード数は縮小してくれる為、テーブルのレコード余りを気にする必要はありません。多い場合でも自動拡張されるので同じです。

ExcelのVBA（マクロ）を実行する

Excel側に既存の処理を行う為のマクロやVBAの処理ルーチンがある場合、あえてVBS側で構築して実行するのではなく、VBS側はそれを呼び出す事に徹する事が可能です。この時必要になるのは、モジュール名とプロシージャ名の２つです。Module1に以下のようなコードがあった場合、今回それをVBS側から呼び出します。

VBA側コード

VBS側コード

このHelloWorldプロシージャをVBSからは以下のような形で呼び出して実行が可能です。

マクロ入ブックなので、必ず対象のファイルは拡張子がxlsm形式である必要があります。そのファイルのフルパスをfullpathへ格納して、実行するわけですが、本来VBAは外部から引数付きで呼び出せないのですが、VBSからは以上のような形で、Application.Runで渡せます。

“Module1.HelloWorld”でModule1のHelloworldプロシージャを呼び出し、その際の引数をstrMsgで渡しています。

ExcelのVBA（マクロ）を実行する２

前述のマクロを実行するコードは、引数付きでExcel VBAに対して引数付きでマクロを実行させています。これを更に発展させて、VBA側からの処理結果を受け取る場合のコードが以下のようになります。こうすることで、相互にデータのやり取りと処理結果を渡せる為、確実な処理を確立する事が可能です。

VBA側コード

今回は、Subプロシージャではなく、Functionにしてあります。返り値の型はVariantで指定します。

VBS側からの引数をstrMsgで受け取って表示後に、HelloWorld = “OKだよ”にて、返り値として返します。これをVBS側で受け取ってくれます。

VBS側コード

retにVBA側からの返り値が入ってきますので、これをMsgBoxで表示させています。その為、Application.Runでは引数はカッコの中に記述する事になります。

特定のプログラムが起動してるかチェック

VBSを実行時に、コントロールしたい別のプログラムが起動しているかどうかのチェックを行うケースがあります。例えば、SAPが起動していないとSAP GUI Scriptingは動かせません。この時、SAPが起動していないのならば起動するといった事が、VBSの一連の作業の中で可能になります。

OSにあるWMIの仕組みを利用して、起動中プロセスをの一覧を取得。その中に「saplogon.exe」が存在していれば起動中。なければ、WScript.ShellにてSAPを起動し、ウィンドウをアクティブにするまでの処理が上記の処理になります。

関連リンク

• PythonでExcelを操作し自動化する方法！ 読み込みや書き込み、グラフまで徹底解説
• 【VBA】テーブルサイズを変更する【Resizeを使います】
• PowerQueryでデータ更新する時に時間がかかる時の対処法
• VBScriptで指定Excelブックを開きマクロを実行する方法
• 【パワークエリ×マクロ】作成したクエリをボタンひとつで順番に更新する方法
• 【Selenium】chromedriverを自動更新するVBSスクリプト