10月 232016
 

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

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


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 はマクロの実行時間を記録するために使用します。


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

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

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


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

  1. 初期化処理
  2. CPU 情報の取得とシートへのセット
  3. Memory 情報の取得とシートへのセット
  4. Disk 情報の取得とシートへのセット
  5. 終了処理

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

Set Wmi = GetObject( _
“winmgmts:{impersonationLevel=impersonate}!\.\root\cimv2” _
)

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


終了処理となります。

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)。


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

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

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


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


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 時間かかってハマりました。

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

8月 042016
 

経緯

先に書いたブログ PowerShell で ClosedXML を使って Excel ファイルを作成する にて、「動作が軽快」と書きましたが体感でしかなかったので、単純に 多くのセルにデータを入れたら速度に差がでるか? というのを試してみたいと思います。

しかし、単純な比較といいなが色々と勉強しながらでした。

特に、一括で貼り付ける方法は「速い」というブログ等は多く目にしましたが、実際にどのようなコードで検証して何と比べて速いのかがまとまっているのは私には見つけられませんでしたので、個人的には今回のサンプルコードがとても大切です。

環境

  • VAIO-Z
    • CPU: Core i7-5557U 3.10 GHz (最大3.40 GHz)
    • Mem: 16GB
    • Storage: 第二世代 ハイスピードSSD(PCI Express x 4 (20 Gb/s))
  • Windows 10
  • PowerShell 5.0
  • ClosedXML 0.76.0
  • Microsoft Open XML Format SDK
  • MS Office 2016

計測方法

Measure-Command を利用して計測する

検証項目と結果

一つのシートで、100 X 100 のセルに下記のパターンでデータを入れていく

No. 試験内容 COM Object ClosedXML
1. セル 1 つづつ 10,000 回、データを入れる(装飾なし) 30.4542562 sec 0.699596 sec
2. セル 1 つづつ 10,000 回、文字色を赤、フォントの大きさを 15 のデータを入れる 136.3952091 sec 0.913028 sec
3. 1 行(100 セル) づつ 100 行分、データを入れる(装飾なし) 2.0920194 sec 0.6800917 sec
4. 1 行(100 セル) づつ 100 行分、文字色を赤、フォントの大きさを 15 のデータを入れる 6.8155114 sec 0.8739652 sec
5. 10,000 のセルに一括でデータを入れる(装飾なし) 1.5663538 sec 0.6527399 sec
6. 10,000 のセルに一括で、文字色を赤、フォントの大きさを 15 のデータを入れる 2.1558577 sec 0.8357458 sec
  • 今回検証に使ったコードは GitHub においてあります

考察

xls 縛りとかがないのなら、ClosedXML を使ったほうがよいなというのが私の感想です。

また、少し面倒に感じますが、配列に入れて一気に貼り付けるほうが圧倒的に速いですので、設計段階からこの考えを取り入れていきたいです。

7月 312016
 

経緯

以前 PowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しい としてブログを書いたことがあります。

その時の方法では、 Excel がインストールされている必要がありましたが、ClosedXML というライブラリを使うと Excel がインストールされていなくても Excel のファイルを作れることを知りましたので、少し試してみたいと思います。

環境

  • Windows 10
  • PowerShell 5.0
  • ClosedXML 0.76.0
  • Microsoft Open XML Format SDK

作業ディレクトリ

作業ディレクトリをデスクトップに作りました。

ClosedXML の準備

まずはサイトでダウンロードします。 zip 形式でダウンロードできます。

ダウンロードしたライブラリにはセキュリティの観点からブロックが施されていますので、解除をして解凍します。

ダウンロードした zip ファイルを右クリックしてプロパティを開き、ブロックの解除にチェックを入れて OK ボタンを押します。

その後、zip ファイルから ClosedXML.dll を取得して、作業ディレクトリに保存します。

Microsoft Open XML Format SDK の準備

ClosedXML のドキュメントページ からも DLL を直接ダウンロードできますが、今回は
Microsoft のサイトから Microsoft Open XML Format SDK をダウンロードします。

ダウンロードページの例

今回私は 2.0 をインストールしてみましたが、私の環境では、msi を実行すると、 C:\Program Files (x86)\Open XML SDK\V2.0\lib に配置されました。

Office のバージョンと関係があるようです。

MS Office Open XML Format SDK
Office 2007 SDK 1.0
Office 2010 SDK 2.0
Office 2013 以降 SDK 2.5

ClosedXML 公式で配布されていたのは、2.0 と 2.5 でした。

Hello world

早速 Hello world を書いてみます。

結果はこうなりました

考察

とても簡単でしたし、動作がとても軽快で驚きました。以前の方法だと、何らかの理由で COM オブジェクトの破棄ができなかった時は、Excel のプロセスが残ってしまうという事がありましたが、その危険性もなさそうです。

気がついたメリット・デメリットは下記の通りです。

メリット

  • 動作が軽快
  • Excel が入っていないマシンでも実行できる 重要
  • オブジェクトの破棄に気を配る必要がない

デメリット

  • xls 形式のファイルは扱えない
  • 環境構築が Excel インストールだけに比べると煩雑