はじめに
PowerShellでも、ほぼVBAと同じ書き方でExcelの編集が行える。
正確には.NET Frameworkを使ってCOMオブジェクトを操作できるということだけど、その構文がほぼVBAと同じなので、VBAと似た感覚で記述できると思う。
PowerShellで書くメリット
バージョン管理しやすくなる
VBAはExcelファイルに内包されており、そのExcelファイル(xlsx)はバイナリ形式ということもあって、VBAのバージョン管理がしづらいことは問題になっている。
それを解消するために、ExcelファイルからVBAスクリプトをエクスポートしてバージョン管理をやりやすくするためのツールも公開されている。
vbacでエクセルVBAのソースコードをGitバージョン管理する方法
しかし、PowerShellならテキスト形式そのものなので、手軽にバージョン管理が行える。
バッチ処理に向いている
夜間に、社内システムから出力されたcsvファイルをExcelに取り込んでおきたい…など、ユーザーがExcelファイルを開いていない間に実行しておきたい編集作業に向いている。
PowerShellで書くデメリット
開かれたブックの編集には不向き
あくまでも外部からExcelファイルを編集するので、ユーザーがブックを開いているときにマクロを実行させたい場合などは不向き。
基本的な書き方
$excel = New-Object -ComObject Excel.Application
$book = $null
try {
# VBAでいう「Application.Visible」などにあたる
# このほか、Applicationオブジェクトから呼び出すメソッド等は$excelから呼び出すことができる
$excel.Visible = $false
$excel.DisplayAlerts = $false
# ブックを開く(VBAと書き方がほぼおなじ
$book = $excel.Workbooks.Open("C:\test\test.xlsx")
# 1シート目を取得(インデックスは1から始まる）
$sheet = $book.Sheets(1)
$range = $sheet.Range("A1")
# セルへの書き込み
$range.Value = "bbbbb"
# セルの値の取得
# Valueの後ろにカッコが必要
Write-Host $range.Value()
[void]$book.Save()
} finally {
if ($book -ne $null) {
[void]$book.Close($false)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($book)
}
# Excelの終了
[void]$excel.Quit()
# オブジェクトの開放
# ApplicationとBookに対して行えばよい
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
}
こうしたいときはどうすれば？を知りたいとき
VBAでの実現方法を調べ、それをそのままPowerShellの構文へ置き換えるだけで大体動く。
プロパティに括弧が必要な場合がある
(@nukie_53様 情報提供ありがとうございます)
例えば、上記サンプルの値の取得で$range.Value()としたように、プロパティを参照する場合でも、引数ありのプロパティを取得する場合は後ろに括弧が必要。VBAでは省略できるのでValueプロパティに引数があることを忘れがちだが、PowerShellで括弧を忘れると意図しない結果が返ってくる。
参照：Range.Value プロパティ (Excel) | Microsoft Docs
↓試してみたコード
Write-Host "括弧なし"
Write-Host $range.Value
Write-Host "括弧あり"
Write-Host $range.Value()
↓出力結果
括弧なし
Variant Value (Variant) {get} {set}
括弧あり
bbbbb
これに対し、Widthプロパティなど引数のないプロパティは、括弧なしでも値が取得できる(括弧ありでも取得ができる)。
Write-Host "括弧なし"
Write-Host $range.Width
Write-Host "括弧あり"
Write-Host $range.Width()
括弧なし
52.8
括弧あり
52.8
VBAの列挙型を使いたいとき
たとえば、VBAでいうRange("A1").End(xlDown)したいとき、xlDownはどうすればよいか。
値を調べて直接使用する
MSのリファレンス(例：xldirection 列挙 (Excel) | Microsoft Docs)を調べたり、VBAのイミディエイトウィンドウでDebug.Printしたりして調べた数値をそのまま使用する。
↓VBAのイミディエイトウィンドウで数値を調べた様子
$sheet.Range("A1").End(-4121)
アセンブリをロードして列挙型を使用する
.NET側にも同じ列挙型が用意されているので、それをロードして使用する。
# 最初に1度ロードすればよい
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
# 型名が長いのでいったん変数に入れる
$xlDirection = [Microsoft.Office.Interop.Excel.XlDirection]
# 列挙型を使う
[void]$range.End($xlDirection::xlDown).Select()
メソッドの引数を省略したいとき
たとえば、シートをコピーするとき、第1引数のBeforeは指定せずに、第2引数のAfterのみ指定したい場合がある。
VBAではWorksheets("Sheet1").Copy After:=Worksheets("Sheet3")と書けばよいが、PowerShellでは以下のようにする。
$sheet.Copy([System.Reflection.Missing]::Value, "Sheet3")
[System.Reflection.Missing]::Valueはメソッドの引数を省略していることを示す値。
コンソールに意図しない「0」などが出るとき
メソッドの戻り値が標準出力に書かれている可能性がある。PowerShellはメソッドや関数の戻り値を変数に格納しない場合、標準出力に出てきたりする。
それを避けるためには、メソッドの頭に[void]を付けるとよい。
[void]$sheet.Range("B2").Select()