PowerShellを使って、Excel資料作成の仕上げを短縮する

概要

Excelを使って、説明や納品する資料を作成したことありますよね。
そんな皆さんは、資料内容を伝えるために様々なことを気を付けているかと思います。

• 内容を簡潔に分かりやすい表現にする
• レイアウトを整える
• 誤字脱字のチェックを行う
• 文体の統一する（「です・ます調」と「だ・である調」）
• 資料を開いたときの状態を整える（開いているシートやスクロール位置など）
• etc...

これらができていないと、「品質が悪い」と思われ評価が下がってしまいます。
今回は、資料を開いたときの状態を整えるにスポットを当てて解決します。

環境

• Windows10
• PowerShell 5
• Excel 2021

機能

PowerShellを使って、Excel資料作成の仕上げを短縮するプログラムを実装しました。
（Excelのマクロだと1ファイル単位でしか処理ができないため、PowerShellを採用しました）

1. ダイアログを開きExcelファイルを指定（複数ファイル可）
2. 指定したExcel資料の仕上げ処理を実行
   • すべてのシート
     • カーソル位置をA1セルを選択
     • スクロール位置一番上へ
     • 拡大縮小を100%
   • 1番左のシートを選択

実行操作イメージ

イメージが伝わりにくいかと思い、PowerShellを実行したときの操作をGif画像にしました。

コード説明

1. ダイアログを開きExcelファイルを指定（複数ファイル可）

ファイル選択のダイアログの起動処理です。
「ダイアログ内にで選択できる拡張子の指定」や「ファイルの複数選択を許可」できるようにしています。

# ダイアログを出して、ファイルを選択する
# @return fileList ファイルリスト
function fileSelect() {
    [void][System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
    $dialog = New-Object System.Windows.Forms.OpenFileDialog
    $dialog.Filter = "Excelファイル形式|*.xlsx;*.xlsm;*.xlsb;*.xltx;*.xltm;*.xls;*.xlt;*.xls;*.xml;*.xlam;*.xla;*.xlw;*.xlr;"

    # 起動時のディレクトリPath
    $dialog.InitialDirectory = Convert-Path .

    # ダイアログウインドウタイトル
    $dialog.Title = "ファイル選択"

    # 複数選択
    $dialog.Multiselect = $true

    # ダイアログ表示
    if($dialog.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK){
        return $dialog.FileNames
    } else {
        return $null
    }
}

2. 指定したExcel資料の仕上げ処理を実行

エクセル仕上げ処理部分です。
重要な箇所を説明していきます。

# エクセル操作初期化
$excel = New-Object -ComObject Excel.Application

# エクセル可視化
$excel.Visible = $False

# 処理ファイルの選択
$itemList = fileSelect
foreach($targetFile in $itemList) {

    # 処理対象ファイル名表示
    Write-Host "FileName:" $targetFile

    # エクセルを開く
    $book = $excel.Workbooks.Open($targetFile)

    # 存在するシート分処理する
    foreach ($s in $book.sheets){
        if ($s.Visible) {
            $sheet = $book.Sheets.item($s.name)
            $sheet.Activate()
            if ($excel.ActiveWindow.FreezePanes) {
                $excel.ActiveWindow.SmallScroll(
                    0,
                    $excel.ActiveWindow.ScrollRow,
                    0,
                    $excel.ActiveWindow.ScrollColumn
                ) | out-null
            }
            $excel.ActiveWindow.Zoom = 100
            $sheet.Range("A1").Select() | out-null
            Write-Host "  SheetName:" $s.name " [Processing completed.]"
        } else {
            Write-Host "  SheetName:" $s.name " [Hidden sheet skip.]"
        }
    }

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

    # 保存
    $book.Save()

    # 閉じる
    $book.Close()

    Write-Host "  Saved`r`n"
}

# 後始末
$excel.Quit()
$excel = $null
[GC]::Collect()

Pause

エクセルの可視化のOFF

処理中のエクセルを表示してしまうと、
処理に時間がかかってしまうのでOFFにしています。

# エクセル可視化
$excel.Visible = $False

非表示シートはスキップ

各シートに対して、「スクロール位置」や「セル選択」や「ズーム値」を指定しています。
非表示シートを処理してしまうと開けずエラーになってしまうので、
$s.Visibleをチェックして、非表示シートはスキップするようにしています。

# 存在するシート分処理する
foreach ($s in $book.sheets){
    if ($s.Visible) {
        $sheet = $book.Sheets.item($s.name)
        $sheet.Activate()
        if ($excel.ActiveWindow.FreezePanes) {
            $excel.ActiveWindow.SmallScroll(
                0,
                $excel.ActiveWindow.ScrollRow,
                0,
                $excel.ActiveWindow.ScrollColumn
            ) | out-null
        }
        $excel.ActiveWindow.Zoom = 100
        $sheet.Range("A1").Select() | out-null
        Write-Host "  SheetName:" $s.name " [Processing completed.]"
    } else {
        Write-Host "  SheetName:" $s.name " [Hidden sheet skip.]"
    }
}

まとめ

プログラムはGitHubに上がっています。ぜひ、資料作成の際に使ってみてください。

バグや追加機能案があれば、コメント頂ければ幸いです。
ここまで見て頂きありがとうございました。