PowershellからAccess VBAのサブプロシージャを実行する（ついでに最適化を行う）方法

Powershellから、マクロを含んだExcelやAccessのサブプロシージャを実行することが出来ます。

Hiddenな動作、例えばExcelで別名ファイルを生成したり、AccessでCSV読み込みとテーブル作成クエリを実行したい場合、元ファイルを開くことなく、Powershellの実行だけで完了するので、自動化の目途が立ちます。

今回はAccessの例を紹介しますが、Excelの場合も殆ど同じですので、試してみてください。

Powershellコード

$dbPath = "$PSScriptRoot\myDatabase.accdb"
$tempDbPath = "$PSScriptRoot\tempDbForCompact.accdb"

$MsAccess = New-Object -ComObject Access.Application
$MsAccess.OpenCurrentDatabase($dbPath)
$MsAccess.Run('Project.Macro')
$MsAccess.CloseCurrentDatabase()
$MsAccess.Quit()

$MsAccess.CompactRepair($dbPath, $tempDbPath, $false)

Move-Item $tempDbPath $dbPath -Force

コードの解説

今回はPowershellのps1ファイルと同じ場所にあるaccdbファイルを操作するため、スクリプト自身のパスを取得します。パスは特別な変数$PSScriptRootに格納されています。

※Powershellのバージョンが古い(2.0以下)場合は下記コマンドで取得します。
Split-Path $MyInvocation.MyCommand.Path -Parent

OpenCurrentDatabaseメソッドでaccdbファイルを開き、Runでプロジェクト名、サブプロシージャ名を指定して実行。プロジェクト名は、デフォルトだとdb1等になっていると思いますが、VBAの画面で確認できます。

AccessでCSVをインポートしてクエリでテーブルを作成すると、確実に容量が大きくなっていきます。これは、テーブルを削除しても容量だけ残ってしまう、Accessの仕様のため。

あなたの会社にもいませんか？「最適化するからみんなデータベース閉じろー！」と叫ぶ人。それならば、インポートをプロシージャにして外からPowershellで叩けば、更新と最適化が同時にできます（業務時間外にスケジュールを組めば尚良）。

最適化は、ボタンからは可能ですが、VBAからはできません。コマンドラインや別ファイルのVBAからは可能です。ネットで調べるとよく見かけるのが、

C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "G:\backups\$Date\file1.mdb" /compact

のようなコードですが…美しくないじゃん？しかもAccessのインストールフォルダが違う環境では動作しません。そこで、

CompactRepair(SourceFile, DestinationFile, LogFile)

を使います。しかし、これも美しくない。最適化中の仮ファイルを指定しなければならないのです。今回のコードでは適当なファイル名にしましたが、本来はその場所に存在しないファイルかをチェックしないとエラーを吐きます。

最後に、仮ファイルで元ファイルを上書きして完了！