みなさん、こんにちは。
ITソリューション事業部の林です。
本記事では、ExcelVBAのテクニックを紹介・解説いたします。
今回紹介するのは、「Windowsコマンドの出力結果を取得」です。
【目次】
それでは、始めましょう!
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シートに展開し、マクロが終了します。
【展開先シート】
終わりに
以上、Windowsコマンドの出力結果を取得するマクロをご紹介しました。
それでは、また次回お会いしましょう!