作業中のメモ

よく「計算機」を使って作業をする.知らなかったことを中心にまとめるつもり.

Power Shell で Excel を操作する(Excel 起動,ファイルオープン,保存,ファイルクローズ)

どうも,筆者です.

たまには,無駄なプログラム(スクリプト)を作成してみようと思う.今回は,「対象とする Excel ファイルを Power Shell から起動し,その後保存して閉じる」というものを作成する.

今回は,以下のような手順で処理する.

  1. Excel アプリケーションを起動する.
  2. Excel ファイルを開く.
  3. Excel ファイルを保存し閉じる.
  4. Excel アプリケーションを終了する.
  5. 後処理

Excel アプリケーションの起動

Power Shell で Excel アプリケーションを起動するには,以下のようにする.

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false      # 画面上に表示させない
$excel.DisplayAlerts = $true # 警告メッセージは表示する

Excel ファイルを開く

続いて,Excel ファイルを開くには,以下のようにする.

# 現在のディレクトリの絶対パスを取得
$currentPath = (Convert-Path .)
# 対象の Excel ファイル名
$filename = "sample.xlsx"
$book = $excel.Workbooks.Open($currentPath + "/" + $filename)

Excel ファイルを保存し閉じる

# 上書き保存
$book.Save()
# ブックを閉じる
$excel.Workbooks.Close()

Excel アプリケーションを終了する

Excel アプリケーションを終了するには,以下のようにする.

$excel.Quit()

後処理

Excel アプリケーションを終了しただけでは,プロセスが残っている.これは,今回の Power Shell で利用した変数を OS が保持しているためである.

このため,変数を破棄する必要がある.ここでは,$book と $excel を変数として利用したため,以下のようにして破棄する.

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($book)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

対象とする Excel ファイルのリストの取得

ある程度自動化したいため,対象とする Excel ファイルをリストにし,テキスト形式で保存する.このテキストファイルを読み込み順に処理する方法を考える.

例として,以下のようなテキストファイルを利用する.これを list.txt として保存する.

sample01.xlsx dummy
sample02.xlsx dummy
sample03.xlsx dummy
sample04.xlsx dummy
sample05.xlsx dummy
sample06.xlsx dummy
sample07.xlsx dummy
sample08.xlsx dummy
sample09.xlsx dummy
sample10.xlsx dummy

list.txt を Power Shell で読み込むには,以下のようにする.

$excelFileList = @(Get-Content -Path ($currentPath + "/list.txt") | %{$_.split(" ")[0]})

「Get-Content」コマンド(Linux の cat コマンド)でファイルの中身を出力する.出力データを 1 行ずつ読み込み,スペースで区切り,最初のデータを配列の要素として取り出す. ここで,「%」は foreach のエイリアスである.また「$_」には,読み込んだ 1 行のデータが格納されている.

作成したスクリプト

作成したスクリプトを以下に示す.

try {
    # 現在のディレクトリの絶対パスを取得
    $currentPath = (Convert-Path .)

    # Excelオブジェクト作成
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false      # 画面上に表示させない
    $excel.DisplayAlerts = $true # 警告メッセージは表示する

    # 対象ファイルの1列目を取り出す
    $excelFileList = @(Get-Content -Path ($currentPath + "/list.txt") | %{$_.split(" ")[0]})

    foreach($filename in $excelFileList) {
        # ファイル名の出力
        Write-Host $filename

        # 対象とするExcelファイル
        $book = $excel.Workbooks.Open($currentPath + "/" + $filename)

        # 上書き保存
        $book.Save()

        # ブックを閉じる
        $excel.Workbooks.Close()

        # 300ms待つ
        Start-Sleep -m 300
    }
    # Excelを閉じる
    $excel.Quit()
}
finally {
    # 変数の破棄
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($book)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
}

追記1(2018/03/04)

Power Shell を起動する際に,実行権限を変更する必要があるため,以下のような bat ファイルを作成した.

@echo off

echo execute Power Shell
powershell -ExecutionPolicy RemoteSigned -file openClose_Excel.ps1

この bat ファイルを動かす場合は,引数を処理して,作業ディレクトリを指定する処理を加える必要がある.Power Shell で引数を取得する方法を以下に示す.

# 現在のディレクトリで初期化
Param ( $argv1 = "." )

# 引数を必須にする場合は,以下のようにする.
#Param ( [parameter(mandatory = $true)]$argv1 = "." )

try {
    # 現在のディレクトリの絶対パスを取得
    $currentPath = (Convert-Path $argv1)

    # 処理が続く
    ...
}