Excel/Calc VBA: 外部プログラム実行
2021/11/06 ※サーバー移行しました: http://cf661650.cloudfree.jp/altmo_toolbox
[Prev] [Next] [Top] , Programming [Prev] [Next]
VBAなら誰でも実行できる
  • ほぼ誰でも持てるプログラム実行環境の一つとして、Microsoft Excelの上で動作するVBA(Visual Basic for Applications)があります。こう書くと「Excelを持ってない人は動かせないけど」と言われそうですが、このVBAはオープンソースOfficeソフトのLibreOffice Calcでも動かせます(*1)。そのためLibreOfficeがWindows/Linux/Mac等マルチOS対応であることも含めて「VBAは誰でも動かせる」と表現しても差し支えないと言えるでしょう。

  • HTTPサーバがあれば(または立てることができれば)、HTML+JavaScript+CGIでGUI的なアプリケーションを作成できますが、セキュリティ上の問題でサーバ構築が無理な場合でも、擬似的なGUIとして表計算ソフトExcel/Calcを利用することもできます。

  • 実は、最近私の仕事場環境がサーバ無し/Windows系のため、VBAを利用する機会が増えています。そこで「これはちょっとメモしておきたい」と思った内容をトピックスとしてまとめることにしました。今回は外部プログラムの実行を扱います。ですが、今回の内容は「まだよくわからないが、2021/11/06時点の理解をまとめ」たものなので、後から何かに気付いて追加/更新するかもしれません。予めご承知下さい。

VBAでコーディングしにくいところは外部ツールを使う
  • プログラミングの基本は連接/選択/反復なので、プログラミング言語の入門書では主にこれらを説明しますが、実は処理対象となるデータへのアクセス方法は「ケースbyケース」ということで説明が省かれていたりします。

  • 例えば「ファイルを探して名前を取得する」といった操作はVBAで可能ですが、実はファイル操作を最も得意とするツールはOSそのもの(*2)です。コマンドプロンプトでDirコマンドを使い、その結果をリダイレクトしてファイル化などは簡単にできます。更にその結果を正規表現を用いて解析したい場合PythonやPerl等「慣れた言語」を使いたいですよね。このように外部ツールを使う機会は多いのです。

  • ここで具体例を見てみましょう。「あるテキストファイルを読み込んで表示したい」とします。VBAで読み込んでシートのどこかに表示します? 単に閲覧したいなら「テキストエディタ」で開くほうが使いやすいですよね。こんな時はWshShellオブジェクトのRunメソッド使用します。基本的な使い方は下記です。 
      Dim obj As Object 'オブジェクトタイプの変数
  Dim ret As Long

  Set obj = CreateObject("WScript.Shell") 'WshShellオブジェクト
  ret = obj.Run(command, 1, False)
' [0]             [1]   [2]  [3]

'[0]戻り値: 正常終了(0),その他(0ではない値)
'[1]実行コマンド文字列: (例)notepad.exe sample.txt
'[2]実行コマンド表示: 表示(1),非表示(0)
'[3]同期実行設定: 同期(True),非同期(False)

  • テキストファイルをnotepad.exeで開きたければ、command文字列に"notepad.exe ファイル名"をセットします。ちなみに関連が登録された拡張子を持つファイルなら、"ファイル名"だけで関連付けされたソフトが動きます。

コマンドプロンプトはcmd.exe...%ComSpec%
  • では、コマンドプロンプトで入力するコマンドを実行したい場合はどうするか、そのときはコマンドインタープリタであるcmd.exeを呼びながらコマンドを与えます。このcmd.exe、コマンドプロンプト上でsetコマンドを実行するとわかりますが、環境変数%ComSpec%で定義されています。

  • 例えばdirコマンドを実行する場合ですが、このcmd.exe(%ComSpec%)に/Cオプションを付けて 　　"%ComSpec% /C dir" のように与えます。しれっと書いてますが環境変数を展開してくれます。また下記のようにパイプも使用できます。 　　"%ComSpec% /C dir | find ""hoge"" " ただしコマンド中でダブルクォーテーションが必要なときはエスケープの「"」を加えるのでちょっとややこしいですね。

コマンドの出力結果を利用する(1)
  • コマンド(標準)出力を取得する方法ですが、先に説明したWshShellオブジェクトのRunメソッドはコマンド出力を取得できません。出力を取得する場合Execメソッドを使用します。

  • コマンド出力を得る/読むには、当然コマンド実行終了を待つ必要があります。これを同期実行と呼びます。Runメソッドでは同期/非同期実行を引数で指定しますが、Execメソッドに該当の指定はありません。しかしExceメソッドは実行状況をWshScriptExecオブジェクトとして返すので、プロパティのStatus(実行中:0,終了時:0ではない値)を監視して同期実行を実現できます。 
    Sub Run_Cmd_GetStdOut()
  Dim objWshShell As Object 'WshShellオブジェクト
  Dim objWshExec As Object 'WshShellExecオブジェクト
  Dim strCmd As String 'コマンド文字列
  
  Set objWshShell = CreateObject("WScript.Shell") 'WshShellオブジェクト取得
  
  'コマンド文字列構成("file_run_target"セルで指定されたバッチファイルを実行)
  strCmd = "%ComSpec% /C " _
         & ThisWorkbook.Path & "\" & Range("file_run_target").Value _
         & " " & ThisWorkbook.Path
  
  Set objWshExec = objWshShell.Exec(strCmd) 'コマンド実行とWshShellExecオブジェクト取得
  
  'WshShellExecオブジェクトのStatus監視(0の間は実行中)タイマー表示
  Dim varStart As Variant: varStart = Now() '開始時刻取得
  Range("file_run_msg").Value = "" '表示初期化
  
  Do While objWshExec.Status = 0 'コマンド実行中のタイマー表示
    Application.Wait [Now() + "00:00:00.01"] '10ms間隔wait
                    'Calcではエラーになる記述
                    'Now() + TimeValue("00:00:01") 1秒単位の書き方までになる
    Range("file_run_msg").Value = "実行中:経過:" _
                                & Format(Now() - varStart, "ss") _
                                & "[sec]"
  Loop
  
  'コマンド実行後の標準出力取得
  Dim strStdOut As String
  strStdOut = objWshExec.StdOut.ReadAll '標準出力取得
  MsgBox strStdOut '結果表示

End Sub

  • 上記マクロについては、sample_2021_0100.zipを展開した後、ディレクトリ内にあるsample_2021_0100_0.xlsmの"E22"セル近辺にある"Run Batch File"ボタンを押してみて下さい。指定された"sample_2021_0100_3.bat"を実行します。このバッチファイルは10秒待った後、Dirコマンドを実行します。

  • そして実行するとわかりますが、実行中はコマンドプロンプトのウィンドウが表示されます...。Runメソッドのように非表示制御はできません。標準出力の結果だけ得たい時はちょっとよろしくないです。

コマンドの出力結果を利用する(2)
  • 標準出力を得るために実行ウィンドウを表示させたくない場合ですが...現状良い案が思い付いていません。ぱっと頭に浮かぶ方法は下記です...。要はファイルを経由しようと。
    • コマンド側では出力結果をファイルに落とす。(リダイレクトも使用できる)
    • Runメソッドでコマンド実行する際、非表示(2番目引数0)、同期実行(3番目引数True)とする。
    • コマンド終了後、ファイルを読んで結果取得する

  • 同期実行なのでコマンドが終了するまでVBA側は待ちます。よってRunメソッドの下に出力ファイル読み込みのステートメントを記述すれば良いです。ただ、この方法...体感的に一瞬で終わる処理なら問題ありませんが、サンプルファイルに入れた実行例のように10秒待つようなケースでは、その間動きが無くなります。正常動作とハングが見分けにくいです。

  • また一時ファイルを使用するため、ファイルの名付けを適当にやってしまうと結果を壊してしまいます。一時ファイルの作成はマナーの良い方法があるはずなので別途調べよう(*4)と思います。

  • また、待ち時間中に操作や表示の処理をしたい場合は、非同期実行でプロセスを監視するしかないかと考えています。実行コマンドのプロセス取得は、得られる結果に対してやや面倒に見えますが、ここは仕方無いということで別レポートにまとめます。

  • 宿題ばかりになりましたが、今回はここまでということで。
Notes
  • CalcでVBAを動かすには"Option VBASupport 1"のOption追記必要ですが、これで「ほぼ」動きます。
  • OS...Operating System...元々はDisc Operating System...。
  • 標準出力ということはテキストファイルですからね。
  • GetTempNameメソッドです。どこかでFileSystemObjectについてまとめようと思っています。
2021/11/06 : 初版
2021/11/14 : Application.WaitのCalc上記述についてコメント追加
Copyright(C) 2021 Altmo
本HPについて
[Prev] [Next] [Top] , Programming [Prev] [Next]