Windowsコマンドの結果をExcelに取り込む方法

Windowsコマンドとは、コマンドプロンプトやPowerShellなどの、WindowsOSに標準搭載されている機能です。
ネットワーク機器との疎通確認(ping)や経路確認(tracert)などが代表的です。

これらコマンドをVBAから動かす場合、「WScript」と、WScriptの「WshShell」クラスを利用して、
コマンド実行ファイル(bat)を実行するという流れになります。

WScriptとWshShellクラスでbatファイルを実行する方法は、過去の記事(№14)で紹介していますので、ご一読ください。

今回は、過去記事で紹介したマクロを応用し、コマンドから得られた結果をExcelシートに取り込むマクロを紹介します。

マクロの構造

VBAマクロの動作としては、大きく分けて以下の3つです。
①batファイルを作成する
②batファイルを実行する
③batファイルの実行結果をExcelシートへ展開する

①②については、過去記事(№14の使用例③)で紹介していますので、③について。

Windowsコマンドでは、「リダイレクト」という仕組みを用いてコマンドの結果をファイルに保存できます。
このリダイレクトを活用してbatファイルで動かし、コマンドの出力結果をファイルに保存、
最後にファイルを読み取ってExcelシートに出力する、という流れになります。

ファイルの内容を読み取る方法は、過去記事(№13の使用例①)で紹介しています。

リダイレクトについて軽く解説します。
コマンドの後ろに、">"もしくは">>"を入れ、最後にファイル名を指定します。
書き方としては、以下のようになります。

新規書き込み：[コマンド] > [ファイル名]
追記書き込み：[コマンド] >> [ファイル名]

新規書き込みのリダイレクトは、毎回ファイルの内容を初期化して書き込みします。
追記書き込みのリダイレクトは、ファイルの内容を保持したまま、末尾の行に書き込みます。

マクロサンプル

以下、サンプルコードです。

Sub ExcelVBA_023_001()
    '①batファイルを作成する
    Dim FileNum
    Dim RowCnt As Long, LastRow As Long
    Dim FilePath As String
    FileNum = FreeFile 'ファイル番号を取得
    FilePath = ThisWorkbook.Path + "\Sample.bat" 'batファイルの保存先パスを指定
    Open FilePath For Output As #FileNum   '書込モードでファイルを開く
    LastRow = Worksheets("Command").Cells(Rows.Count, 1).End(xlUp).Row 'ExcelシートA列の最終行を取得
    For RowCnt = 1 To LastRow '1行目から最終行まで繰り返し
        Print #FileNum, Worksheets("Command").Cells(RowCnt, 1).Value 'ExcelシートA列の値を書き込み
    Next
    Close #FileNum 'ファイルを閉じる
    '②batファイルを実行する
    Dim objWSH As Object
    Set objWSH = CreateObject("WScript.Shell")
    objWSH.Run ThisWorkbook.Path + "\Sample.bat", 1, True
    '③batファイルの実行結果をExcelシートへ展開する
    Dim Cnt As Long
    Dim BufLine
    FileNum = FreeFile 'ファイル番号を取得
    FilePath = "C:\Users\Public\Documents\Result.txt" 'テキストファイルの保存先パスを指定
    Open FilePath For Input As #FileNum   '読込モードでファイルを開く
    Worksheets("Result").Cells.Clear '展開先シートを初期化する
    Cnt = 0 '読み込み行数のカウンタ
    Do Until EOF(FileNum) '読込終了(EOF)まで繰り返す
        Line Input #FileNum, BufLine '1行読込み
        Cnt = Cnt + 1 '読込み行数をカウントアップ
        Worksheets("Result").Cells(Cnt, 1).Value = BufLine '読み込んだ文字列をExcelシートへ転記する
    Loop
    Worksheets("Result").Activate '展開先シートを表示する
    Close #FileNum 'ファイルを閉じる
    MsgBox "Finish.", vbInformation '処理終了メッセージ表示
End Sub

実行するコマンドは、CommandシートのA列に記載しておきます。
ここではecho,ping,tracertコマンドを利用しました（各コマンドの解説は省略）。

【実行コマンド】

echo 日付： %date% > C:\Users\Public\Documents\Result.txt
echo 時刻： %time% >> C:\Users\Public\Documents\Result.txt
ping 127.0.0.1 >> C:\Users\Public\Documents\Result.txt
tracert 127.0.0.1 >> C:\Users\Public\Documents\Result.txt

コマンド実行ファイルは「Sample.bat」という名前で作成し、マクロと同じフォルダに保存します。
コマンドの結果は「C:\Users\Public\Documents\Result.txt」に出力されます。
最後に、Result.txtの文字列をResultシートに展開し、マクロが終了します。

【展開先シート】