PowerShellでエクセルをA1セルにフォーカスを当てた状態で保存する

やりたいこと

カレントフォルダの全てのエクセルファイルに対して下記の処理を行う。

• 全シートの表示倍率を100%にする
• 全シートのフォーカスをA1セルにする
• 一番左のシートをアクティブにした状態で保存する

ソースコード

sample.ps1

$dir = Convert-Path .
$regex = ".*\.xl(s|t|a|w|r|sx|sm|sb|tx|tm|am)$"
$filePathList = Get-ChildItem $dir -File | Where-Object { $_.Name -match $regex } | ForEach-Object { $_.FullName }
try {
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplAyalerts = $true
    $filePathList | ForEach-Object{
        $filePath = $_
        $updateLinks = 0
        $isReadOnly = $false
        $book = $excel.Workbooks.Open($filePath, $updateLinks, $isReadOnly)
        $book.Sheets | ForEach-Object{
            $sheet = $_
            if ($sheet.Visible) {
                $sheet.Activate()
                $excel.ActiveWindow.Zoom = 100
                $sheet.Cells.Item(1, 1).Select() | Out-Null
            }
        }
        $book.Sheets.Item(1).Activate()
        $book.Save()
        $isSaveChanges = $true
        $book.Close($isSaveChanges)
    }
}
finally {
    $excel.Quit()
    $sheet = $null
    $book = $null
    $excel = $null
    [System.GC]::Collect()
}

実行例

実行例

~/Documents/サンプル
❯ ls
Book1.xlsx
sample.ps1

~/Documents/サンプル
❯ ./sample.ps1

~/Documents/サンプル
❯

エクセルが更新されていることを確認

説明

カレントフォルダにあるエクセルファイルの一覧を取得する

# カレントフォルダを取得
$dir = Convert-Path .

# 取得対象ファイルの拡張子を指定する（エクセルで開けるファイルの拡張子）
$regex = ".*\.xl(s|t|a|w|r|sx|sm|sb|tx|tm|am)$"

# ファイル一覧をフルパスで取得する
$filePathList = Get-ChildItem $dir -File | Where-Object { $_.Name -match $regex } | ForEach-Object { $_.FullName }

Get-ChildItem $dir -Fileでファイル一覧を取得する。
$dirにはカレントフォルダを指定しているため、カレントフォルダのファイル一覧を取得する。
-Fileオプションを指定しているためファイルのみを取得する。（フォルダは対象外としている）

Where-Object { $_.Name -match $regex }で絞り込みを行う。
$_.NameはGet-ChildItem $dir -Fileで取得したファイル一覧のファイル名のみを取得する。
-match演算子は正規表現の比較を行う。
$regexにはエクセルで開けるファイルの拡張子を格納している。
よって、$_.Name -match $regexではファイル名をエクセルで開けるファイルの拡張子で絞り込みを行っている。

ForEach-Object { $_.FullName }でファイルのフルパスを取得する。
※Where-Object { $_.Name -match $regex }で絞り込んだ後のファイルのフルパスを取得している。

エクセルを起動する

# エクセルを起動する
$excel = New-Object -ComObject Excel.Application

# エクセルを可視化しない
$excel.Visible = $false

# アラートを表示する
$excel.DisplAyalerts = $true

ブックを開く

# リンクを更新しない
$updateLinks = 0

# 読み取り専用で開かない
$isReadOnly = $false

# ブックを開く
$book = $excel.Workbooks.Open($filePath, $updateLinks, $isReadOnly)

シートを編集する

$book.Sheets | ForEach-Object{
    $sheet = $_

    # 表示されているシートのみ処理の対象にする
    if ($sheet.Visible) {

        # シートをアクティブにする
        $sheet.Activate()

        # シートの表示倍率を100%にする
        $excel.ActiveWindow.Zoom = 100

        # A1セルにフォーカスを当てる
        $sheet.Cells.Item(1, 1).Select() | Out-Null
    }
}

ブックを閉じる

# 1番目のシートをアクティブにする
$book.Sheets.Item(1).Activate()

# ブックを上書き保存する
$book.Save()

# 変更を保存する
$isSaveChanges = $true

# ブックを閉じる
$book.Close($isSaveChanges)

エクセルを終了する

# エクセルを閉じる
$excel.Quit()

# メモリを解放する（これをしないとプロセスにエクセルが残り続ける）
$sheet = $null
$book = $null
$excel = $null
[System.GC]::Collect()