2022年4月12日 / 最終更新日時 : 2022年9月14日 officeの杜 msoffice

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化して配布は可能ですが、そういったタスクをつくり手に要求されます）
  9. 学習コストはVBA・VBSよりもPythonのほうが全然高いです。

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

※Node.jsでもExcel読み書きのライブラリはありますが、同じようなネタになっていない辺りが、最近のPython + Excelネタの実態と言えます。

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

ライブラリを使って読み書きして嵌った事例

前述のお話はPythonでライブラリを使って書き込むという事例のお話ですが、自分もアプリケーションの自動化処理の１つのタスクとしてNode.js + xlsx-populateで読み書きを実装するシーンがあります。読み取り分にはなんら問題ないですし、書込みも一見すると問題なく書き込めているので便利なライブラリなのですが、1例致命的な事例がありました。

書き込みしたファイルは壊れているわけでもなければ、見た目も問題ないのですが、このファイルをPower Queryの外部ソースとして呼び出して利用しようと思うと「DataFormat.Error」と出て、Binaryという文字列が表示されているエラーに遭遇しました。問題点を徐々に絞り込んだら、xlsx-populateで書き込んだ時点でエラーが出ており、xlsxファイルに何らかの潜在的な問題（互換性の問題）が生じてるようです。

自分の場合日付でもなんでもない、GUIDと作業中という文字を書き込んでるだけにも関わらず発生しており、一度手動で開いて何か記述して上書き保存すると読み込めるようになりましたが、Power Queryで使う予定のあるものを、外部のライブラリで読み書きするとこういった事例にぶつかる事もあることを頭に入れて置かなければなりません（公式のライブラリでもなんでもないわけで、この問題の絞り込みも相当な時間を消費しています）

故に、現在書込みについてだけは、xlsx-populateのようなライブラリではなくVBScriptでExcelを操作して書き込ませています。こういったポイントがPythonのExcel操作ライブラリに無いとは言い切れないですし、将来発生してもユーザはなかなかこれに気が付けないでしょう。

基本的な知見は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してる処理になります。

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

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

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

返り値を返す

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

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

処理をスリープさせる

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

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

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

データ領域を選択して消去する

Excel VBAにある「データのある領域を自動選択する」メソッドである「UsedRange」がVBSでも使えます。これを利用してシートの全データを消したい場合があります。以下のようなコードを実行することで、自動的に選択領域を消去してくれます。

上記の場合そのシートのデータ全てが消えてしまうので、テーブル等もタイトル行も消えてしまいます。2行目以降がデータ部分であるならば、通常は以下のようなコードを書くべきでしょう。

表を検索し特定の値を書き換える

表の中で合致するIDの行のステータス欄の値を書き換えたいといったようなケースで利用します。割と遭遇ケースの多いパターンかと思います。その場合、Excelのデータ1行づつを拾っては読み検証では遅すぎるので、まずは「データの塊を配列として取得」してから、その配列に対して検索を実行し、一致した行の位置を特定、その後対象のアドレスに対して値を書き込み保存するといった手順になります。

  • UsedRangeで表データを丸ごと配列としてmaindataに格納しています（当然タイトル列も含んでいる）
  • 今回の事例だとタイトル列を除いてデータ部分の開始行位置は3行目からなので、Forループも3から開始させます。
  • maindataの中身を取り出し、指定のrecidと一致するかどうか？検証し、一致したら対象のRangeのF列の値を「終了済み」に書き換えています。
  • 書き換え完了してるので、Exit Forでループを抜ける

書き換える場合、1行分の二次元配列を用意してセル単位ではなく行単位で上書きといったことも可能です。

テーブル・クエリの操作

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

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

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

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

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

テーブルの有無をチェックする

前述のテーブル化のコードは、便利なのですが事前に「テーブル化」していた場合エラーとなってしまいます。そこで、まずテーブル化を実行する前に、対象のシートに存在するテーブルの数をカウントして、1以上の場合は処理をスルーするようにすると良いです。通常１シート１テーブルが定石なので、この判定を持って、テーブル化を実行すると良いでしょう。

ListObjects.Countで設定されてるテーブルの個数を調べる事ができます。

特定の列の値を変換する

基幹業務システムから吐き出されるExcelデータやCSVは、どういうわけか、数値の列なのに文字列型になったまま吐き出されて、二次利用する際の大きな障害になってるしょうもないシステムが世の中ゴロゴロしています。このシートをテーブル化しても文字列型のままなので、二次利用時にvlookupやxlookup、テーブルの連結をする場合面倒です。

ということで、特定の列の値を数値⇒文字列型に変換して貼り付ける方法を作りました。１行ずつ処理すると8000件程度のレコードでも膨大な時間が掛かりますが、この手法ならば一瞬で終了します。

  • 行数+1の配列を作成し、そこに値を格納し、最後にC列に貼り付けています。
  • arrはオブジェクトであって配列ではないので、arr(0)としても値は取れません。
  • ループで取得した値をCLngで数値型に変換し配列に格納してあげています。
  • 二次元配列ですが1列分しかないので、testarr(cCnt,0)として二次元目は0としています。

特定のテーブルデータを移動＆削除する

これまでのExcelのVBA等でも特定のシートのデータの塊から、ステータスが終了済みのものを別のシートにコピーして、メインのシートからは削除するといったような作業というのは意外と現場では多く存在しました。しかし、これらの処理をVBAで行う場合「配列がどうたら」「Redimがどうたら」と、他の言語と比較してかなりハードルが高く非常に面倒な作業でした。

しかし、テーブルにしてある場合は驚くほど簡単に実現が可能で、VBSからもその作業は行うことが可能です。以下のコードは6列目のデータが「終了済み」の場合、該当データは「過去ログシート」に追記コピーして、「メインシート」からは削除するというコードです。配列など意識する必要もありません。かなり高速で処理が完了するので、RPAなんかでループを使って云々など使ってられないです。

特定のテーブルデータを移動＆削除する２

前述のコードで基本的には、特定列の値を元にデータの移動と削除は可能ですが、以下のようなケースの場合、挙動がおかしくなります。

  1. 移動元のテーブルにそもそもレコードが1件もないケース
  2. 移動元のテーブルに該当するレコードが無い場合、何故か全部のレコードが過去ログに移動してしまう（フィルタが効かない為）

当然起こりうるケースですので、このケースに対応した移動と削除の実現は以下のようにする。

  • まず、メインのテーブルのレコード件数を調べる為に、ListRows.Countで調査する
  • レコード数が0ならば何もしないで終了する
  • レコードが1件以上ある場合、テーブルの「ステータス列」の値だけをListColumns("ステータス").DataBodyRangeで取り出す
  • レコード件数が1件の場合は、arrの値を直接判定して、終了済みならばCntを加算する
  • レコード件数が複数の場合は、For Eachで回して、終了済みならばCntを加算する
  • 1件の場合と複数の場合でDataBodyRangeの型が異なる為、上記のように２つのケースで処理を変えてあります。
  • Cntの値が1件以上ある場合は、終了済みレコードが存在するので、レコードの移動と削除を実行する

やや複雑ですが、テーブルの各メソッドの特性上このようにすることで、トラブルに対処する事が可能です。

特定のテーブルデータを移動＆削除する３

前述までは通常のテーブルのデータ。つまり、数式などが含まれていない純粋なデータの場合は問題なく動作します。しかし、数式の入ってるデータでCopyを行ってしまうと、移動先の数式がそのまま入った状態でコピーされるため、#Valueなどのエラーが移動先で表示されることになります。

Excelの値のみ貼り付けという形でコピーをする必要性があります。その手法は以下の通りになります。

  • .Copyまでは同じ。但し貼り付けについては、過去ログシートの一番下の行に対して、PasteSpecial(-4163)にて貼り付けると、値貼り付けになる
  • .Copyの引数として指定するとエラーになるので行を変えています。
  • 削除については分けて実行しています。

PasteSpecialの引数に指定する値によって、貼り付けパターンを変更することが可能です。-4123を指定すれば数式貼り付けになります。このタイプの一覧はこちらのサイトにあります。

テーブルデータをフィルタして削除する

前述までの手法で、テーブルデータに対してフィルタをして削除は実現できているのですが、実はひとつ大きな問題点があり「フィルタする対象となるワード」が該当しない場合、「.EntireRow.Delete」にて削除すると全部のデータが削除されてしまいます。必ずキーワードとなるものを含んだデータが存在している必要があります（データは表示されていないのですが削除は実行されるという）

そこでこれに対応した形でフィルタした結果のレコード件数を調べて「空」ならば処理をしないという処理を入れる必要があります。VBAの場合この処理は「.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count」を使うことで件数を調べる事が可能なのですが、VBSではこの構文ではなく、「.DataBodyRange.Columns(1).SpecialCells(12).Count」という構文となるので注意が必要です（SpecialCellsの12がxlCellTypeVisibleに該当する）。

  • rnDataの行でフィルタ後のテーブルデータの表示されてる件数を調べています。
  • データがない場合は、空が返ってくるので、その場合はフィルタを解除する
  • データがある場合は.EntireRow.Deleteでテーブルデータを削除しフィルタを解除する
  • temprecの中身が空の場合はそもそも処理をせずスルーするようにしています。（そうしないと、全表示されて削除されてしまう）

フィルタした結果のデータを修正する

上記のテクニックでフィルタした後、表示されてるデータに対して「値の編集」をしたい場合、重要なのは表示されてる行のアドレスの取得になります。当然全ての行のアドレスが連続してるわけではないので、アドレスを取得し、その値を持ってしてデータを修正した後解除するという手順が必要になります。

また、その場合のアドレスが起点のAからとなるため、他の列の値となると必要なのは列名を除いた数値となるので、ここを正規表現で取り出します。

  • 表示されてるレコードの進捗率を100にするの処理が今回のメインとなる部分です
  • .Columns(1).SpecialCells(12).Rowsにて、1列目の行データを取り出しています
  • 取り出したオブジェクトの.Addressにて、絶対参照のアドレス（例：$A$12）といったような値が取れます。
  • 正規表現にて数値だけを取り出します。
  • 取り出した数値はmatcher.valueに入ってるのでこれを利用してRangeで指定し、値を100に書き換えています。

フィルタを複数設定する

前述までのコードはテーブルの1列だけをフィルタして作業をしています。しかし、複数条件でフィルタをしたい場合には、いくつかのテクニックがあります。より正確に高速にフィルタをしてから作業をしたい場合、以下のようなコードを設定します。

2列に対してフィルタを設定する

上記のケースは2列に対して同時にフィルタをする事で、絞り込みをしています。

1列に対して2つのORフィルタを設定する

上記のケースは1列に対して、1030と1160の2つをフィルタで指定しています。本来VBAの場合は、xlOrを使うのですがVBSの場合はこちらの一覧にあるように、数値を指定します。2つ目の2はxlOrと同じ意味になります。xlAndならば1となります。

1つの列に対して3つ以上の値でフィルタする

前述のケースは2個の値だけだったので、2を指定してxlOrとしてフィルタ出来ましたが、これが3つ以上となると、なかなか不便です。ということで、配列を使って、まとめてフィルタしたい値をセットして抽出する事が可能です。

Arrayで配列を用意して複数のフィルタしたい値をセットする。これをAutoFilterにセットし、こちらの一覧にあるxlFilterValuesを意味する7を指定する。注意点は数値であっても、Arrayの中ではダブルコーテーションで括って文字列としないとこの場合フィルタされない。

フィルタを全解除する

テーブルのフィルタが掛かった状態で他からデータを追加しようとすると「シート行全体を削除しますか？」というダイアログが毎回出てしまい、自動化の阻害になります。そのため、VBSで自動化をする上では、事前に対象のテーブルの全オートフィルタを解除しておく必要があります。

テーブルに対して、AutoFilter.showalldataを実行することで全部のオートフィルタが解除されます。必ずコピー前に入れておきましょう。オートフィルタされてなくともエラーにはなりません。

図：ダイアログが出て実行が止まってしまう

数式を復元する

前述までのフィルタして過去ログ移動は大変便利なのですが、移動した結果コピー元のテーブルから１行もデータが無くなることもあります。問題なのはこのテーブルには行を追加時用に各種数式が入ってる場合、数式も空っぽになりますので、次回以降入力する際に困ったことになります。そこで、移動後にテーブルのレコード数を調べて0だった場合には、VBSにて数式を補完する処理を入れておくと良いでしょう。

ただし、数式内のダブルコーテーションも含めて構築をするため、ダブルコーテーションをエスケープする必要性があります。１個のダブルコーテーションは「""」と２つで表現するようになります。Formulaにて数式を入力する事が可能です。

注意点として、対象の列の書式設定が「文字列」になっている場合、数式が入らず数式がそのまま文字列として入ってしまいますので注意。なるべくテーブルでは書式設定は使わず、Power Queryの型の変換を利用するようにしましょう。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

BackgroundQuery:=Falseのオプションを入れることで、バックグラウンドで更新するにチェックをいれた状態と同じ指定になります。

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

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

Power Queryの更新完了を待つ

前項ではVBSからPower Queryを更新することが可能になりましたが、この処理の後に例えばブックを閉じるであったり、別の作業を行わせている場合、時として更新完了を待たずに次の処理へと進んでしまい、「この操作を実行すると、まだ実行されていないデータの更新が取り消されます。続けますか？」と、エラーが出ることがあります。

また、保存して閉じる処理を入れてるのに、先に進んでしまってるために「保存しますか？」とダイアログが出てしまう事もあります。これを制御する為に、Power Queryの更新が完了するまで処理を待機させる処理を入れる必要があります。この処理にはQueryTable.Refreshingというプロパティの値を参照して、完了してるかどうかを判定します。

図：終わってないのに閉じようとしてしまった時のエラー

また、手っ取り早い方法は、以下のように設定してから、wbo.RefreshAllすれば確実に更新待してから処理が可能です。ソースコードとしては

クエリ側の設定としてはクエリと接続⇒対象のクエリを右クリック⇒プロパティを開いてから「バックグラウンドで更新」のチェックを全てのクエリで外しておきます。

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

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

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

まず、ListRows.Countでテーブル内のレコード量を把握します。これに対して、+500分だけテーブルを拡張しておきます。現在のデータ領域までピッタリ拡張したい場合には、ListRows.Countではなく、wbo.Sheets("シート名").UsedRange.Rows.Countを使うと良いでしょう。

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

テーブルをデータ範囲にフィットさせる

前述のようにピッタリのデータを入れてる分には問題ありませんが、大きめに拡張した後にデータの実数がテーブルの範囲よりも少ない場合、テーブルに空白行ができてしまいます。このような場合には、見た目もよろしくないですが、UsedRange.Rows.Countなどで行数を取った場合に、空白行もカウントされてしまいます。

このようなケースに於いて、最後の処理として実際のデータ範囲に合わせてテーブルを縮小してあげる必要があります。以下のようなコードで実際のテーブル範囲にフィットさせる事が可能です。

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を起動し、ウィンドウをアクティブにするまでの処理が上記の処理になります。

関連リンク

カテゴリー
msoffice、RPA、業務テクニック
タグ

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。

LibreOffice

前の記事

最新のM1 Macを徹底活用する為のノウハウ
2022年4月6日
Microsoft365

次の記事

VBAとMicrosoft Graph APIの連携 – Planner編
2022年4月13日