PowerShell 第8回 ExcelマクロやExcelアドインを実行

1. はじめに

ExcelマクロやExcelアドインで、Excel作業を効率化することが多いです。
多くはExcelファイルを操作しますが、CSVファイルを処理するのにもExcelを使うことがあります。
本当にExcelとマクロは便利です。
そんな中、「今存在するCSVファイルを処理するExcelマクロをExcelを起動しないでワンクリックで実行したい。」という要望を同僚からもらい、PowerShellをクリックしてExcelアドインを呼び出して実行してみようと思いました。

何故PowerShell？というのはあると思います。
Excelマクロの自動実行でもできるし、VB Scriptから呼び出すこともできるし、やり方は色々ありますから、PowerShellを使う必要性はありません。
そこは、PowerShellを使いこなしたいという心でしょうか。
ということで、勉強ふまえて、あえてPowerShellからExcelアドインを呼び出してみることにしました。

もうひとつ、PowerShellはWindowsにデフォルトで実装しており、追加のインストールがいらないんです。
これは仕事で使う上ではとても重要で、ほぼ誰でも使えるんです。
ということで、試したことをいくつか残しておこうと思ったのでこの記事を作ることにしました。

2. 今回実施する内容

PowerShellからExcelアドインやExcelマクロを呼び出して、その中のSub関数やFunction関数やClassなどを呼び出します。

3. ソースコード(Git Hub)

4. 環境

OS: Windows 10 JP (64bit)
PowerShell version: 5.1.19041.1

5. 参考

なし

6. 用語

COM

Component Object Model。
マイクロソフトが提唱するソフトウェアの再利用を目的とした技術。

7. 本編

7.1 PowerShellからExcel.Applicationのオブジェクトの生成と終了

PowerShellからExcelファイルを読み込むためには、COMオブジェクトを利用します。

#COMオブジェクトの生成
$xlApp = New-Object -ComObject Excel.Application

#COMオブジェクトの解放
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($xlApp) | Out-Null

• COMオブジェクトの生成は、VB ScriptなどでもCOMオブジェクトは使いますが、それをPowerShellの文法で記載するだけです。
  $xlAppを使って、Excel.Applicationオブジェクト配下のメソッドやプロパティが使えるようになります。
  いたって簡単です。これでほとんどExcelを操作できるといっても過言ではありません。
  ここで、Excel自体は起動されていますが、画面には表示されません。
  画面表示させるとしたら、以下です。

#COMオブジェクトの生成
$xlApp = New-Object -ComObject Excel.Application
$xlApp.Visible = $True

#COMオブジェクトの解放
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($xlApp) | Out-Null

• COMオブジェクトの解放は、少し見慣れないですが、読み込んだ$xlAppをFinalReleaseComObjectで解放するということです。Out-Nullは出力を出さないようにしているだけです。

7.2 Excelファイルのオープン、クローズ

Excelファイルのオープン、クローズは、以下の通りです。

$file = "C:\temp\test.xlsm"
#ブックの読み込み
$xlBook = $xlApp.Workbooks.Open($file)
#ブックのクローズ
$xlBook.Close()

$xlAppの後のWorkbooks.OpenやCloseの記載は、Excel VBAそのものです。
上記では、ファイルを開いてすぐさまクローズしています。
今回はExcelマクロを開きましたが、Excelアドインでも動作は同じです。

7.3 Excel関数の呼び出し

Excel関数を使用するには、$xlAppや$xlBookなどを使用して利用できます。

$xlBook.Worksheets("Sheet1").Range("A1").Text
$xlBook.Worksheets("Sheet1").Range("A1").End(-4121).Text
$xlBook.Worksheets(1).Cells(3,3).Value  = "60"
$xlApp.Range("A1").Text

上記の通り、Excel関数の呼び出して、値の取得や値の代入は普通にできます。
ただし、For文やIf文はPowerShellのものを使う必要がありますし、VisualBasicの関数はこのままでは利用できません。
VisualBasicの関数の呼び出しは、7.6 Visual Basic関数の呼び出しに記載します。

7.4 Sub関数やFunction関数の呼び出し

Sub関数やFunction関数の呼び出しです。
まずは、呼び出し元のSub関数Function関数のサンプルを作成します。
test.xlsmにModuleを追加して、メッセージを表示するだけのものを作成します。
この内容説明は割愛します。

test.xlsm

Option Explicit

Public Sub SubShowMessage(strMessage As String)
    Call MsgBox(strMessage)
End Sub

Public Function FuncShowMessage(strMessage As String) As String
    Call MsgBox(strMessage)
    FuncShowMessage = strMessage
End Function

Public Function FuncGetCell(strCellAddress As String) As Range
    Set FuncGetCell = Range(strCellAddress)
End Function

上記関数をPowerShellから呼び出します。

#SubShowMessageのSub関数を実行
$xlApp.Run("test.xlsm!SubShowMessage", "Hello world")

#FuncShowMessageのFunction関数を実行
$resultMessage = $xlApp.Run("FuncShowMessage", "Bye world")
#resultMessageの表示
Write-Host $resultMessage
    
#FuncShowMessageのFunction関数の戻り値をパイプ処理してWrite-Hostで出力
$xlApp.Run("FuncShowMessage", "Bye Bye") | Write-Host

#FuncGetCellのFunction関数で、A1セルのRangeを取得
$range = $xlApp.Run("FuncGetCell", "A1")
Write-Host $range.Text

Sub関数やFunction関数は、Excel.Application.Runメソッドで実行できます。

• 第一引数がSub関数名やFunction関数名です。
  [ファイル名]![関数名]
  と書いてもいいし、
  [関数名]
  だけでも大丈夫です。
  例えば、ファイルを複数開いて処理する場合は、[ファイル名]![関数名]のように記載したほうが良いと思います。複数ファイルを開いて同一関数名が存在する場合、どちらの関数が動作するかわかりません。
• 第二引数以降は、実行する関数の引数を設定します。
  引数はVariantですから、RangeなどExcelの型でも受けられます。
• Function関数の戻り値は、変数に入れてもいいし、パイプ処理でも動作するようです。しかし、本当にパイプ処理をしようと思ったら、うまく動作しないような気はします。VBAのほうが処理が終わってからしか戻り値を返しませんから、試していないですが、実質使えないだろうと思います。
• Rangeなどオブジェクト変数も戻り値として取得可能ですから、FuncGetCellのような戻り値の型がRangeでも処理可能です。例ではA1セルのRangeを取得して、その値をWrite-Hostに出力しました。
  Rangeの場合は、$xlApp.Range("A1")などで取得できますから、今回は単なる例です。

結果は、当たり前ですが、メッセージボックスに関数で設定した文字列が表示されます。
Wite-Hostは、画面出力されます。

7.5 Classの呼び出し

Classの呼び出しです。
まず、呼び出し元のClassを作成します。
test.xlsmにClassを追加して、メッセージを表示するだけのHelloClassを作成します。

test.xlsmのHelloClass.cls

Option Explicit

Private Const CLASS_NAME As String = "HelloClass"

Public Property Get ClassName() As String
    ClassName = CLASS_NAME
End Property

Public Sub Hello(strName As String)
    MsgBox ("Hello " & strName)
End Sub

上記の内容説明は割愛しますが、１つ重要なことは、InstancingをPrivateからPublicNotCreatableに変更することです。

今回PowerShellからtest.xlsmの呼び出しを実行します。当たり前ですがこの二つは別のファイルであり、Privateの範囲では動作しません。PublicNotCreatableに変更する必要があります。
NotCreatableがついていますので、Publicアクセスはできますがインスタンスは作れません。インスタンスはモジュール内で作成します。
参考ですが、Privateのままで下記記載のPowershellを実行すると以下のようなエラーが発生します。

"1" 個の引数を指定して "Run" を呼び出し中に例外が発生しました: "HRESULT からの例外:0x800A0062"
発生場所 C:\temp\test.ps1:23 文字:5
+     $myClass = $xlApp.Run("CreateHelloClass")
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : COMException

上記のClassを呼び出すモジュール内の関数は以下の通りです。

test.xlsm

Public Function CreateHelloClass() As HelloClass
    Set CreateHelloClass = New HelloClass
End Function

上記HelloClassをモジュール経由で、PowerShellから呼び出します。

$myClass = $xlApp.Run("CreateHelloClass")
Write-Host $myClass.ClassName
$myClass.Hello("Japan")

HelloClassのインスタンス生成は、上記のモジュールのFunction関数で作成します。
作成したインスタンスのクラス名と単なるメッセージボックスの表示だけを行いました。

7.6 Visual Basic関数の呼び出し

VB関数の呼び出しです。
VB関数を呼び出すには、そのアセンブリを読み込む設定が必要です。
読み込んだうえで関数を呼び出します。

Add-Type -AssemblyName Microsoft.VisualBasic
[Microsoft.VisualBasic.Interaction]::MsgBox("Test")
[Microsoft.VisualBasic.Strings]::Len("Test")

Add-Typeで読み込むアセンブリを記載します。今回はVBの関数を呼び出したいため、Microsoft.VisualBasicとしています。
そのあとは、MsgBoxやLen関数を使用しています。
その前に付与している[Microsoft.VisualBasic.Interaction]や[Microsoft.VisualBasic.Strings]は名前空間の定義であり、このように毎回長い記載が必要です。
これを避ける場合は、usingを使用して名前空間を定義すれば多少回避できます。

using namespace Microsoft.VisualBasic;
...
Add-Type -AssemblyName Microsoft.VisualBasic
[Interaction]::MsgBox("Test")
[Strings]::Len("Test")

8. おわりに

PowerShellからExcelマクロを実行しました。
トライしてみて確かに実行はできるけれど、面倒なのでかなりいまいちかなとは思いました。
既存のExcelマクロを外部から呼び出したいとき程度で使うくらいかなと思いました。
今回もともｔそういう要望で実施しましたので、それについては問題なく動作してよかったとは思いました。