想定する利用シーン

前回の記事では、pingとtracertコマンドの出力結果をテキストファイルに保存し、
続いてExcelシートに取り込む、というマクロを紹介しました。

実際にインフラ構築現場で利用することを考えると、
「1つのコマンドごとに出力結果をテキストファイルに保存し、それを繰り返す」
というマクロが求められることがあります。

例えば、以下のような表に記載されたホストとの疎通確認を行う場合が挙げられます。
下記のホストはすべてパブリックDNSサーバーです。

ホスト	IPアドレス
GoogleDNS	8.8.8.8
OpenDNS	208.67.222.222
CloudflareDNS	1.1.1.1
Quad9DNS	9.9.9.9

これらに対しpingとtracertコマンドを投入し、出力結果を保存します。

マクロの構造

VBAマクロの動作としては、大きく分けて以下の3つです。
①batファイルを作成する
②batファイルを実行し、出力結果をファイル保存する
③Excelシートの表にあるホストすべてに対して①②を繰り返す

①②については、過去記事(№23)で紹介しています。
③はFor-Nextで繰り返し実行します。

マクロサンプル

Excelシートには、以下のようにホスト一覧の表を作成しておきます。
A列はホスト名、B列はIPアドレスです。
1行目は項目名、2行目以降にホスト名とIPアドレスを記入します

マクロのコードは以下になります。
ホスト名とIPアドレスは変数に記憶しておき、コマンドの引数や出力先ファイルとして利用します。

Sub ExcelVBA_024_001()
    Dim FileNum
    Dim RowCnt As Long
    Dim LastRow As Long
    Dim StrBatFilePath As String
    Dim StrHost As String
    Dim StrIP As String
    Dim StrOutFile As String
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row '表の最終行を取得する
    For RowCnt = 2 To LastRow
        StrHost = Cells(RowCnt, 1).Value 'ホスト名を変数に記録
        StrIP = Cells(RowCnt, 2).Value 'IPアドレスを変数に記録
        FileNum = FreeFile 'ファイル番号を取得
        StrBatFilePath = ThisWorkbook.Path + "\Sample.bat" 'batファイルの保存先パスを指定
        StrOutFile = "C:\Users\Public\Documents\疎通確認_" + StrHost + ".txt" 'コマンド出力結果ファイルの保存先パスを指定
        'batファイルを作成する
        Open StrBatFilePath For Output As #FileNum   '書込モードでファイルを開く
        Print #FileNum, "echo 日時： %date% %time% > " + StrOutFile 'echoコマンドをファイルへ書き込み
        Print #FileNum, "ping " + StrIP + " >> " + StrOutFile  'pingコマンドをファイルへ書き込み
        Print #FileNum, "tracert " + StrIP + " >> " + StrOutFile 'tracertコマンドをファイルへ書き込み
        Close #FileNum 'ファイルを閉じる
        'batファイルを実行する
        Dim objWSH As Object
        Set objWSH = CreateObject("WScript.Shell")
        objWSH.Run StrBatFilePath, 1, True
    Next
    
    MsgBox "Finish.", vbInformation '処理終了メッセージ表示
    objWSH.Run "C:\Windows\explorer.exe ""C:\Users\Public\Documents""", 1, True '指定したフォルダを開く
End Sub

マクロの実行中はコマンドプロンプトの画面が開いたままになります。