PowerShellで簡単にExcel触れるようにする方法を考えてみた

前書き

様々な情報がExcelで管理されていると、VBAを書かないと難しい作業が発生したりする。
ただ、VBAは他のプログラミング言語に慣れていると書きにくく感じたり、管理が面倒だったり、使い回しにくかったりで個人的には極力書きたくない。
某リラックスなツールなどのアドインで解決できる場合もあるが、やはりプログラマブルに扱いたい場合もあるので、PowerShellからExcelを簡単に触れるようにする方法を考えてみた。

VBAの代わりにPowerShellを使うようにすると以下のようなメリットがあります。

• PowerShellの文法でVBAの関数が使える
• 一般的なPowerShellのスクリプトと同じなので、使い回しやすくなる
• コードをExcelから独立させられる
  • テキストデータになるため、管理しやすくなる

デメリットは情報が少ないこと。
VBAの情報はたくさんあるけれども、それをPowerShellで扱えるように読み替えるのが慣れるまで結構難しい。

コードは以下Gist参照
https://gist.github.com/sensq/49c2edb87c6346229fb9cc62deb90ea2

PowerShellでExcelを開く

ググった感じでは、ComObjectでExcelオブジェクトを作ってExcelオブジェクトにあるOpenメソッドに開きたいExcelファイルのパスを渡してOpenするのが一般的っぽい。
バッチ処理のような場合はこの方法で問題ないと思うが、"今開いている"Excelファイルに対してPowerShellからコマンドを実行したい場合は意外と手間である。
パスを調べたり引数を書き換えたりの作業も手間であるが、パスワードのかかっているファイルだったり共有フォルダ上に置かれていて誰かが開いていたりすると例外処理を考える必要が出てきたりしてうんざりしてしまう。
VBAで書く場合もこれは同様の悩みだと思う。
ただし、前にも書いた通り、既に開いているExcelファイルであれば起動中のプロセスからPowerShellでExcelファイルを呼び出すことが可能である。
この方法であればパスをわざわざ指定する必要が無く、余計な例外処理も考えること無く、簡単に"今開いている"Excelファイルに対してPowerShellからコマンドを実行することができる。

コード

利便性を考えながら試行錯誤して、以下の関数を作成した。
PowerShellのバージョンは5.0を使用
確認していないけど、たぶん3.0以上なら動く気がする。
（Out-GridViewのPassThruオプションが3.0から実装されたものらしいので）

Function EXCEL-Get-Book([String]$book_name){
<#
  .SYNOPSIS
  既に開かれているExcelブックからブックオブジェクトを選択して出力する関数
#>
  Function Get-Book($book_name){
    # Excelオブジェクトを取得
    try{
      $ex = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
    }catch{
      throw "Excelが起動していません"
    }
    
    # 引数が指定されていた場合は指定された名前のブックを返す
    if($book_name){
      foreach ($bk in $ex.WorkBooks){
        if ($bk.Name -eq $book_name){
          return $bk
        }
      }
    }
    else{
      # 開いているブックの名前をすべて取得
      $book_list = @()
      $ex.WorkBooks | %{
        $book_list += $_.Name
      }
      
      # 一つしか開いていなかったら自動的にそのブックを返す
      if($book_list.length -eq 1){
        return $ex.WorkBooks
      }
      
      # 二つ以上開いていた場合はGridViewで選択された名前のブックを返す
      $ret_bk = $book_list | Out-GridView -PassThru -Title "開くブックを選択して下さい"
      foreach ($bk in $ex.WorkBooks){
        if ($bk.Name -eq $ret_bk){
          return $bk
        }
      }
    }
  }

  # 指定されたブックを開く
  $global:EXCEL_BOOK = (Get-Book $book_name)
  if($global:EXCEL_BOOK -eq $null){
    throw "指定された名前のブックが開かれていません"
  }
  
  Write-Host ('Get $global:EXCEL_BOOK = '+ $global:EXCEL_BOOK.Name)
  $global:EXCEL_BOOK
}

• ブックが複数開かれている場合、Out-GridViewでブック名のリストを表示し、選択された名前のブックをPowerShellで取得する
• ブックが一つしか開かれていない場合、自動的にそのブックをPowerShellで取得する
• 引数が指定された場合、指定された名前のブックをPowerShellで取得する

取得したブックオブジェクトはそのまま関数のリターンとして返されるようにしているので、変数に入れれば後はVBAのようにブックを触ることができる。
PowerShellの文法が使えるので、シート全部をループさせたい場合は以下のように%（ForEach）を使って簡単に書くことができる。

$book = EXCEL-Get-Book
$book.WorkSheets | %{$_.Name}

# 一時変数を使わずにパイプでそのまま渡してもOK
EXCEL-Get-Book | %{$_.WorkSheets | %{$_.Name}}

# グローバル変数にも入れるようにしているため、以下のような書き方でもOK
EXCEL-Get-Book
$global:EXCEL_BOOK.WorkSheets | %{$_.Name}

例えば、「全シートの選択セルをA1にする」という処理は例外処理を何も考えなければ以下のように書ける。

$global:EXCEL_BOOK.WorkSheets | %{$_.Activate();$_.Cells.Item(1,1).Select()}

おまけ

ついでに、以下の関数も作成してみた。

Function EXCEL-Get-BookList(){
<#
  .SYNOPSIS
  既に開かれているExcelブックのブック名をすべて出力する関数
#>

  # Excelオブジェクトを取得
  try{
    $ex = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
  }catch{
    throw "Excelが起動していません"
  }
  
  # 開いているブックの名前をすべて取得
  $book_list = @()
  $ex.WorkBooks | %{
    $book_list += $_.Name
  }

  $book_list
}

Function EXCEL-Get-BookALL(){
<#
  .SYNOPSIS
  既に開かれているExcelブックからすべてのブックオブジェクトを出力する関数
#>
  EXCEL-Get-BookList | %{
    EXCEL-Get-Book $_
  }
}

EXCEL-Get-BookALLを実行すると起動中のブックすべてをPowerShellで開くことができる。
これをForEachで回すと、すべてのブックのすべてのシートに同様の処理を行うことができる。

例えば、前述した「全シートの選択セルをA1にする」という処理をすべてのブックに行いたければ以下のように書ける。

EXCEL-Get-BookALL | %{$_.WorkSheets | %{$_.Activate();$_.Cells.Item(1,1).Select()}}

他にも遊びで色々と汎用的に使えそうな関数を作ってみたので、せっかくだから公開しておきます。
https://gist.github.com/sensq/49c2edb87c6346229fb9cc62deb90ea2

以前公開した以下のコードも若干リファクタリングして入れてあります。
PowerShellでExcelの表をオブジェクト化して取得