PoweShellからVBAを定期的に実行する設定

猫も杓子もローコード・ノーコードでRPAな昨今、自分もできる範囲でRPAやってみようと思い手始めに毎朝のルーチンワークに手を付けてみました。

例題
仕組構成
VBA処理内容
PowerShell起動方法
PowerShellコード
タスクスケジューラへの登録
テスト実行

例題

毎朝行っている下記のルーチンワークをRPAしてみました。

会計ソフトから条件指定して仕訳データCSVダウンロード
上記ダウンロードしたCSVファイルをSQLにInsert

上記の内、１はAutoHotKeyというソフトで実現しましたが当blogでは割愛。

仕組構成

具体的には下記の構成で実現します。

仕組	役割
タスクスケジューラ	PowerShellを定期的に実行する
PowerShell	CSVファイル移動とVBAの起動
Excel VBA	SQLiteの古いデータを削除後、CSVファイルをSQLiteにInsert

当記事では定期的にPowerShellからExcel VBAを実行する方法を紹介します。

VBA処理内容

コードは割愛いたしますが下記の処理を行っています。

SQLiteから指定した月の仕訳データを全て削除
会計仕訳データCSVから不要列を削除
残ったデータをSQLiteにInsert

後でPowerShell側でオブジェクトの解放を行っている(つもりな)のですが、上手く解放されません。

VBA側でコードの最後に「Application.Quit」を記載するとちゃんと解放されました。

他の状況次第ですが、おまじないで記載すると良いと思います。

PowerShell起動方法

PowerShellを起動します。

「Windowsキー + R」でファイル名を指定して実行を起動し、

powershell_ise と入力してOK。

PowerShellコード

起動したPowerShellのスクリプトウィンドウにコードを記載。

スクリプトウィンドウが表示されていなければ、「表示」→「スクリプトウィンドウの表示」を選択

ルーチンワーク_VBA実行.ps1

move-item "ダウンロードしたCSVファイルのフルパス" "移動先のフォルダパス" -Force

$excel = $null
$excel = New-Object -ComObject Excel.application 
$book = $excel.workbooks.open("起動したいマクロを含むExcelブックのフルパス") 
$excel.run("呼び出すマクロ名","引数１の値")

$book.close
$excel.quit
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($book) | Out-Null
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel) | Out-Null

Move-Itemコマンド

第1引数(PowerShell的にはパラメータ？)で指定したフォルダやファイルを、第2引数のフォルダに移動させます。

引数 -Force は移動先に同一名のフォルダ・ファイルが存在する場合に上書きを行います。

PowerShellでExcelマクロ実行

PowerShellでは変数の頭に「$」を記述します。

変数excel・変数bookへの代入はVBAと同じような感じで使用できます。

ExcelマクロはExcelオブジェクト.Run で実行できます。

引数を渡したい場合は第2引数以降に指定することができます。

オブジェクトの解放

’[System.Runtime.InteropServices.Marshal]’::FinalReleaseComObject(オブジェクト変数) でオブジェクト変数を開放することができます。

コードの記載が終わったら適当な場所へ保存してください。

タスクスケジューラへの登録

タスクスケジューラの起動・準備

まずタスクスケジューラを起動します。

Windowsキー → Windows管理ツール → タスクスケジューラ

タスクスケジューラを開くと既に色々なタスクが登録されていると思います。

これからの作業中に誤って触ってしまうと怖いので、別フォルダを作成してその中で作業します。

「タスクスケジューラライブラリ」で右クリック → 新しいフォルダー

任意のフォルダ名を設定

他にタスクが登録されていないフォルダが作成されました。

タスクの登録

タスクの登録

タスクスケジューラの右側メニュー「タスクの作成」をクリック

「全般」タブで任意の名前をつける

トリガーの登録

「トリガー」タブでタスクの起動をスケジューリングします。

ここで指定したタイミングでPowerShellが実行され、Excelマクロも実行されます。

実行するタスクを登録

「操作」タブの新規をクリック

「操作の編集」画面では、「プログラム/スクリプト」と引数の追加を指定します。

項目	内容	記載
プログラム	PowerShellのフルパス	%Systemroot%\SysWOW64\WindowsPowerShell\v1.0\powershell.exe(64ビット版) %Systemroot%\System32\WindowsPowerShell\v1.0\powershell.exe(32ビット版)
引数	実行するマクロブックのフルパス	-ExecutionPolicy Bypass マクロのフルパス

ExecutionPolicy

デフォルトの状態ではタスクスケジューラからPowerShellを正常に実行することが出来ません。

ExecutionPolicyを設定せずにタスクスケジューラからPowerShellを実行すると、下記のように結果が表示されますが、実際は実行されていません。

ExecutionPolicyはPowerShellのセキュリティ設定で無差別にスクリプトを実行できないようになっています。

デフォルトでは「Restricted」になっているためスクリプトが実行できません。

そこでデフォルトの設定はそのままで、実行時に一時的に「Bypass」を指定することでスクリプトを実行できるようにしています。

設定名	内容
Allsigned	署名済みスクリプトを実行
Bypass	何もブロックされず、警告やプロンプトは表示されません
Restricted	個々のコマンドを許可しますが、スクリプトは許可できません
Unrestricted	署名されていないスクリプトを実行できます。悪意のあるスクリプトを実行するリスクがあります