Excel マクロはタスクスケジューラで実行できるのか ～Excel だけでパフォーマンスログを収集する～

2020/04/18

Excel マクロはタスクスケジューラで実行できるのか

PowerShell でログ収集・精査をして、確認用の Excel に保存する。
「Excel が自分自身でやれよ」と考えてしまい、「そもそも Excel のマクロをタスクスケジューラーから実行できるのかな？」というところから確認してみようと思い試してみました。

体調崩していて暇ですし、ただ試すだけでは面白味にかけたので、ついでに簡易的なパフォーマンスログ取得を行なってみます。「パフォーマンスモニター使えば？」と思われる方もいるでしょうが、楽しくやっているのでそっとしておいて下さい＾＾；

数カ月ぶりにマクロを触ると作り方等をすっかり忘れていたので、個人的な備忘録としても、私が分かる範囲で少し詳しめに書いてみたいと思います。ご指摘があれば優しくお願い致します。

やること

  • Excel マクロで、CPU、Memory、Disk の状態を取得する
  • Excel マクロをタスクスケジューラーで実行する

検証環境

  • Windows10 Pro
  • Excel 2016

1. マクロ有効ブックの作成

ちなみに作成後のファイルはこちらになります
手っ取り早く試してみたい方は、上記ファイルを使い、 2. タスクスケジューラーへの設定 から読むことをおすすめします。

1-1. 下準備

マクロを作る前に

  1. 上部メニューの ファイル をクリック
  2. 左部メニューの オプション をクリック
  3. Excel のオプションウィンドウ、左部メニュー リボンのユーザー設定 をクリック
  4. 中央部の左リストから 開発 を選択し、 追加 >> ボタンをクリック、その後 OK ボタンをクリック
    開発をする時には、「開発」のリボンを表示した方が便利です。

データ用シートの作成

マクロから行っても良いのですが、1 回しか行わないので手作業で作りました。

  • cpu シート

    列 値 書式設定
    A 名前 文字列
    B 使用率 数値
    C 取得日時 ユーザー定義、 yyyy/m/d hh:mm:ss

  • memory シート

    列 値 書式設定
    A タイプ 文字列
    B 空き容量 数値
    C 全体容量 数値
    D 取得日時 ユーザー定義、 yyyy/m/d hh:mm:ss

  • シート

    列 値 書式設定
    A タイプ 文字列
    B 空き容量 数値
    C 全体容量 数値
    D 取得日時 ユーザー定義、 yyyy/m/d hh:mm:ss

まずはマクロなしの素の Excel ファイルとして保存しました。
ここで作成された Excel ファイルはこちらになります >> performance.xlsx

1-2. マクロを組む

「開発」リボンから、Visual Basic
をクリックすると次のようなウィンドウが開きます。

左のウィンドウにある ThisWorkbook をダブルクリックして、その中にマクロを記述していきます。
今回私が書いたコードはこちらになります >> ThisWorkbook.vbs

需要があるか判りませんが少し説明をしてみます。

' used disk-sheet
Const DISK_NAME = 1
Const DISK_FREE = 2
Const DISK_TOTAL = 3
Const DISK_CREATEDAT = 4

' used memory-sheet
Const MEM_NAME = 1
Const MEM_FREE = 2
Const MEM_TOTAL = 3
Const MEM_CREATEDAT = 4

' used cpu-sheet
Const CPU_NAME = 1
Const CPU_PERSENT = 2
Const CPU_CREATEDAT = 3

' WMI Object (connect to localhost)
Dim Wmi As Object

' Now datetime
Dim CreatedAt As Date

Const DISK_NAME = 1
　　：
Const CPU_CREATEDAT = 3

最初に各シートで使う列を定数（Const）で宣言しています。

もしソースコード上で列を表す数値を直接書いていると変更に弱くなります(例えば列を入れ替えた時にプログラムの変更箇所が多い等)ので、予め定数として宣言しておきます。

Dim Wmi As Object
今回は CPU、Memory、Disk の各情報を WMI（Windows Management Instrumentation）経由で取得しますので、オブジェクト型として Wmi を定義しています。

Dim CreatedAt As Date
CreatedAt はマクロの実行時間を記録するために使用します。

'
' auto running
'
Sub Workbook_Open()
    Application.WindowState = xlMinimized
    Call Logging
End Sub

ファイル(ブック)を開いた時にマクロを自動実行するメソッドです。

Application.WindowState = xlMinimized
起動時に最小化します。

Call Logging
Logging サブルーチンを呼び出しています。

'
' main
'
Public Sub Logging()
    Call Initialize
    Call SetInfoForCpu
    Call SetInfoForMemory
    Call SetInfoForDiskdrive    
    Call Terminate
End Sub

Logging サブルーチンでは、次の処理を呼び出しています。

  1. 初期化処理
  2. CPU 情報の取得とシートへのセット
  3. Memory 情報の取得とシートへのセット
  4. Disk 情報の取得とシートへのセット
  5. 終了処理
'
' Initialize process
'
Public Sub Initialize()        
    Application.DisplayAlerts = False

    ' Set WMI Object
    Set Wmi = GetObject( _
        "winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2" _
    )

    ' Set Now datatime
    CreatedAt = Format(Now, "yyyy/mm/dd hh:nn:ss")
End Sub

Application.DisplayAlerts = False
このような警告メッセージを出さないようにしています。
alert.png

Set Wmi = GetObject(
"winmgmts:{impersonationLevel=impersonate}!\.\root\cimv2"
)
WMI をローカルコンピューター(動作させる PC)の root\cimv2 名前空間に接続しています。
VBA で複数行に分けて書きたい時には、上記のように _ (アンダースコア)でつなげます。

'
' Terminate process
'
Public Sub Terminate()

    Set Wmi = Nothing
    ThisWorkbook.Save
    If Workbooks.Count = 1 Then
        Application.Quit
    Else
        ThisWorkbook.Close
    End If
End Sub

終了処理となります。

Set Wmi = Nothing
Wmi オブジェクトの破棄を行います。

ThisWorkbook.Save
ファイルの保存を行います。

If Workbooks.Count = 1 Then
Application.Quit
Else
ThisWorkbook.Close
End If
他の Excel ファイルが開かれていない時に Excel の終了（Application.Quit）、他に開いている Excel がある時には今のファイルだけ閉じています（ThisWorkbook.Close）。

'
' Set cpu information to Datasheet
'
Public Sub SetInfoForCpu()
    Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("cpu")
    Dim Row As Long: Row = Sheet.Cells(Rows.Count, CPU_NAME).End(xlUp).Row + 1

    Dim Items As Object: Set Items = Wmi.ExecQuery("SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor")
    Dim Item As Object

    For Each Item In Items
        If Item.Name <> "" Then
            Sheet.Cells(Row, CPU_NAME).Value = Item.Name
            Sheet.Cells(Row, CPU_PERSENT).Value = Item.PercentProcessorTime
            Sheet.Cells(Row, CPU_CREATEDAT).Value = CreatedAt
            Row = Row + 1
        End If
    Next

    Set Item = Nothing
    Set Items = Nothing
    Set Sheet = Nothing
End Sub

SetInfoForCpu は CPU 情報の取得とシートへのセットを行っています。

Row = Sheet.Cells(Rows.Count, CPU_NAME).End(xlUp).Row + 1
データを記録できる行を取得するため、「データのある次の行」を Row にセットしています。

その後 WMI から取得したデータを Cell にセットしていき、最後に使用したオブジェクトを破棄しています。

'
' Set memory information to Datasheet
'
Public Sub SetInfoForMemory()
    Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("memory")
    Dim Row As Long: Row = Sheet.Cells(Rows.Count, MEM_NAME).End(xlUp).Row + 1

    Dim Items As Object: Set Items = Wmi.ExecQuery("SELECT * FROM Win32_OperatingSystem")
    Dim Item As Object

    For Each Item In Items
        If Item.FreePhysicalMemory <> "" Then
            Sheet.Cells(Row, MEM_NAME).Value = "PhysicalMemory"
            Sheet.Cells(Row, MEM_FREE).Value = Item.FreePhysicalMemory
            Sheet.Cells(Row, MEM_TOTAL).Value = Item.TotalVisibleMemorySize
            Sheet.Cells(Row, MEM_CREATEDAT).Value = CreatedAt
            Row = Row + 1
        End If

        If Item.FreeVirtualMemory <> "" Then
            Sheet.Cells(Row, MEM_NAME).Value = "VirtualMemory"
            Sheet.Cells(Row, MEM_FREE).Value = Item.FreeVirtualMemory
            Sheet.Cells(Row, MEM_TOTAL).Value = Item.TotalVirtualMemorySize
            Sheet.Cells(Row, MEM_CREATEDAT).Value = CreatedAt
            Row = Row + 1
        End If
    Next

    Set Item = Nothing
    Set Items = Nothing
    Set Sheet = Nothing
End Sub

SetInfoForMemory は Memory 情報の取得とシートへのセットを行っています。
Memory には「物理メモリ」と「仮想メモリ(swap)」があり、物理メモリを PhysicalMemory、仮想メモリを VirtualMemory としてデータをセットしています。

'
' Set diskdrive information to Datasheet
'
Public Sub SetInfoForDiskdrive()

    Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("disk")
    Dim Row As Long: Row = Sheet.Cells(Rows.Count, DISK_NAME).End(xlUp).Row + 1

    Dim Items As Object: Set Items = Wmi.ExecQuery("SELECT * FROM Win32_LogicalDisk")
    Dim Item As Object

    For Each Item In Items
        If Item.DeviceID <> "" Then
            Sheet.Cells(Row, DISK_NAME).Value = Item.DeviceID
            Sheet.Cells(Row, DISK_FREE).Value = Item.FreeSpace
            Sheet.Cells(Row, DISK_TOTAL).Value = Item.Size
            Sheet.Cells(Row, DISK_CREATEDAT).Value = CreatedAt
            Row = Row + 1
        End If
    Next

    Set Item = Nothing
    Set Items = Nothing
    Set Sheet = Nothing
End Sub

Disk 情報の取得とシートへのセットは CPU と Memory の時とほぼ同じです。

1-3. マクロの保存

名前をつけて保存を行い、ファイルの種類を *Excel マクロ有効ブック(.xlsm)** を選択して保存します。
ここで作成された Excel ファイルはこちらになります >> performance.xlsm

2. タスクスケジューラーへの設定

タスクスケジューラーへの設定を試みましたが上手く動いてくれず、調べると全く同じ状態でブログに情報を載せてくださっている方がいました。ありがとうございます！

これらを踏まえて設定していきます。

2-1. タスクスケジューラの起動

タスクスケジューラを起動します。
Cortana に「タスク」と入力したら出てくると思います。

2-2. 設定

  • 操作の タスクの作成 をクリックします。

　

  • 任意の名前を入力
  • ユーザーがログオンしているときのみ実行する を選択

　

  • 設定を 1 回
  • 繰り返し間隔を任意の間隔で入力
  • 継続時間は無期限を選択

　

  • プログラム/スクリプトに次の値を入力
    "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE"

  • 引数の追加(オプション) に次の値を入力
    <マクロ有効ブックの絶対パス> /e

    • 例: C:\Users\hoge\Desktop\performance.xlsm /e

    /e を付けることで、Excel の起動画面が表示されません。

設定は以上ですので、OK でタスクの作成を完了させて下さい。

3. 実行

テスト実行する時には、登録したタスクを右クリックして「実行する」を選択すると実行されます。
その際、 初回の実行の時にマクロの警告がでますので許可してください。

今回作成したマクロは、起動すると自動で実行して閉じてしまいますので、中のデータを確認する時はマクロが動かないように開く必要があります。

  1. Excel を開く (ファイルをダブルクリックではない)
  2. ファイルを開く時に Shift ボタンを押しながら開く

10-1.png

10-2.png

考察

Windows Server 2008 R2 ですんなり上手く行っていたタスクスケジューラの設定が、Windows 10 で動作が変わっていることに気付くのに 2 時間かかってハマりました。

実際に使う時には、グラフ化しやすいようにデータを取った方が良いですね。

ProgrammingExcel, VBA

Posted by みやみや