PowerShellでExcelを操作したときに詰んだこと

初心者の備忘　随時追加（予定）

$sheet.Activate()しないとエラーになる

例

#excelをOpenして$sheetに対象Worksheetを代入した後で

$sheet.Range("A1") = $text #これは動く
$sheet.Cells.Item(1, 1).Copy() #これも動く
$sheet.Cells.Item(1, 1).Select() #これはエラー

こういうエラーが出る

07/13/2023 10:07:23 Range クラスの Select プロパティを取得できません。
発生場所 C:\script\test.ps1:87 文字:7
$sheet.Cells.Item(1, 1).Select() | Out-Null
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : OperationStopped: (:) [], COMException
FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

「Range クラスの Select プロパティを取得できません」と、なんだかよくわからないエラーが出るが実態はSheetをActivate()しなければならない

下のようにすれば動く

+ $sheet.Activate()
$sheet.Range("A1") = $text
$sheet.Cells.Item(1, 1).Copy()
$sheet.Cells.Item(1, 1).Select()

$sheet.Cells.Item(1, 2).Paste() がエラーになる

例

$sheet.Cells.Item(1, 1).Copy()
$sheet.Paste($sheet.Cells.Item(1, 2)) #これは動く
$sheet.Cells.Item(1, 2).PasteSpecial() #これも動く
$sheet.Cells.Item(1, 2).Paste() #これはエラー

Copyと同じ要領でPasteを書くとエラーになる。PasteSpecialは動く。

07/13/2023 10:50:57 [System.__ComObject] に 'Paste' という名前のメソッドが含まれないため、メソッドの呼び出しに失敗しました。
発生場所 C:\script\テスト\test.ps1:92 文字:7
$sheet.Cells.Item(1, 2).Paste() | Out-Null
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : InvalidOperation: (Paste:String) []、>RuntimeException
FullyQualifiedErrorId : MethodNotFound

Paste関連まとめ

$sheet.Cells.Item(1, 1).Copy()

$sheet.Paste($sheet.Cells.Item(1, 2)) #動く

$sheet.Cells.Item(1, 2).Paste() #エラー

$sheet.Cells.Item(1, 2).PasteSpecial() #動く

$sheet.PasteSpecial($sheet.Cells.Item(1, 2)) #エラー


$sheet.Activate()
$sheet.Cells.Item(1, 2).Select()
$sheet.Paste() #動く

$sheet.Activate()
$sheet.Cells.Item(1, 2).Select()
$sheet.PasteSpecial() #動く

$sheet.Cells.Item(1, 1).Copy($sheet.Cells.Item(1, 2)) #動く

（余談）Excelを保存した時のセルの選択状態にきをつけろ

Paste、PasteSpecialは範囲指定なしでも動く
この場合、最後にExcelを保存したときに選択されているセルにペーストするので
意図しない場所にペーストしないよう気を付けなければならない

例

$sheet.Paste() #動く
$sheet.PasteSpecial() #動く

Range.Address でセルのアドレスを取得できない

Range.Address()　カッコが必要なのでエラーになる
VBAだとRange.AddressでもOKなのかサンプルコードがわらわら出てくるけどpowershellではだめっぽい

例

$range = $sheet.Range("A1")
Write-Host $range.Address()
Write-Host $range.Address($true, $true) 
Write-Host $range.Address($false, $true) 
Write-Host $range.Address($true, $false) 
Write-Host $range.Address($false, $false) 
Write-Host $range.Address #意図した結果にならない

#出力結果：
#$A$1
#$A$1
#$A1
#A$1
#A1
#string Address (Variant, Variant, XlReferenceStyle, Variant, Variant) {get} 

Rows.UsedRangeとColumns.UsedRangeでループをうまく回せない

Columns側にUsedRangeはいらない

NG→OK

$sheet.UsedRange.Rows | ForEach-Object {
-    $_.UsedRange.Columns | ForEach-Object {
+    $_.Columns | ForEach-Object {
        Write-Host $_.Text
    }
    Write-Host "End of Row"
}

(Excelだけじゃないけど)ForEach-ObjectではContinueできない

ForEach-Objectループを継続したい場合はcontinueではなくreturnを使う
（continueだとループが終わる）

NG→OK

$sheet.UsedRange.Rows | ForEach-Object {
    if ($_.Columns(1).Text -eq "りんご") {
        Write-Host "Skip the row"
-        continue
+        return
    }

    $_.Columns | ForEach-Object {
        Write-Host $_.Text
    }
    Write-Host "End of the Row"
}