Excel で AllowFiltering を指定するとシートが保護されない(Powershell)


Powershell から COM Object 経由で Excel のシート保護をする(Protect メソッドを呼ぶ)時に、AllowFiltering や AllowUsingPivotTables を指定しようとするとシートが保護されない問題が発生しました。Optional 引数のデフォルト値の指定方法に癖があるようです。それの対処法を紹介します。

AllowFiltering は大分後ろの方にあり、それ以前の引数はデフォルト値を使ってほしいです。VBA だと途中の引数のスキップは簡単ですが、 PowerShell だとそれはできません。調べたところ、 こちらにデフォルト値指定の方法が載っていました。[Type]::Missing を使うようです。

function protect($sh) {
    $default = [Type]::Missing
    $sh.Protect(
          $default #Password
        , $default #DrawingObjects
        , $default #Contents
        , $default #Scenarios
        , $default #UserInterfaceOnly
        , $default #AllowFormattingCells
        , $default #AllowFormattingColumns
        , $default #AllowFormattingRows
        , $default #AllowInsertingColumns
        , $default #AllowInsertingRows
        , $default #AllowInsertingHyperlinks
        , $default #AllowDeletingColumns
        , $default #AllowDeletingRows
        , $default #AllowSorting
        , $default #AllowFiltering
        , $default #AllowUsingPivotTables
    )
}

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$sh = $wb.Sheets('Sheet1')
protect($sh)
保護されない

この状態だとなぜかシートが保護されません。そこで、1つずつ引数を外して調査したところ、ちょうど使用したかった AllowFiltering の手前に境界があることが分かりました。以下の状態だと想定通り動くのですが、 AllowFiltering のコメントを外すとシートが保護されません。

function protect($sh) {
    $default = [Type]::Missing
    $sh.Protect(
          $default #Password
        , $default #DrawingObjects
        , $default #Contents
        , $default #Scenarios
        , $default #UserInterfaceOnly
        , $default #AllowFormattingCells
        , $default #AllowFormattingColumns
        , $default #AllowFormattingRows
        , $default #AllowInsertingColumns
        , $default #AllowInsertingRows
        , $default #AllowInsertingHyperlinks
        , $default #AllowDeletingColumns
        , $default #AllowDeletingRows
        , $default #AllowSorting
        #, $default #AllowFiltering
        #, $default #AllowUsingPivotTables
    )
}
期待通りの動作。丁度使いたいところが指定できないもどかしさ

Optional 引数の default 指定方法に問題があると予想し、今度はすべて値を入れてみたところ、こちらはうまく動きました。

function protect($sh) {
    $sh.Protect(
          "" #Password
        , $false #DrawingObjects
        , $true  #Contents
        , $true  #Scenarios
        , $false #UserInterfaceOnly
        , $false #AllowFormattingCells
        , $false #AllowFormattingColumns
        , $false #AllowFormattingRows
        , $false #AllowInsertingColumns
        , $false #AllowInsertingRows
        , $false #AllowInsertingHyperlinks
        , $false #AllowDeletingColumns
        , $false #AllowDeletingRows
        , $false #AllowSorting
        , $false #AllowFiltering
        , $false #AllowUsingPivotTables
    )
}

今回は全部値指定した方がコードもすっきりするのでいいですが、他のメソッド呼ぶときも考えるとデフォルト指定できないのは不便です。なのでもう少し調べてみたところ、このようなサンプルコードに出くわしました。[ref] を付けるようです。こちらも期待通り動作しました。

function protect($sh) {
    $default = [Type]::Missing
    $sh.Protect(
          [ref]$default #Password
        , [ref]$default #DrawingObjects
        , [ref]$default #Contents
        , [ref]$default #Scenarios
        , [ref]$default #UserInterfaceOnly
        , [ref]$default #AllowFormattingCells
        , [ref]$default #AllowFormattingColumns
        , [ref]$default #AllowFormattingRows
        , [ref]$default #AllowInsertingColumns
        , [ref]$default #AllowInsertingRows
        , [ref]$default #AllowInsertingHyperlinks
        , [ref]$default #AllowDeletingColumns
        , [ref]$default #AllowDeletingRows
        , [ref]$default #AllowSorting
        , [ref]$default #AllowFiltering
        , [ref]$default #AllowUsingPivotTables
    )
}

COM Object 系はドキュメントが少ないので、こういった不具合に出くわすと地味に時間がかかります。ドキュメントの充実を期待したいところですが、最近は Power Platform や Web 版 Office など、クラウドに移行しつつあるので難しそうです。COM 利用しているスクリプトも移行を検討しようと思います。


コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください