PowerShellでExcelをRC形式で呼び出し繰り返し実行する

PowerShellでは.NetFrameworkのオブジェクトを処理することができます。同じマシンの中にMicrosoft Officeが入っていれば、例えば外部からExcelをComObjectとして操作してやることができます。せっかくscriptを組んで処理するのですから、「1行ごとに処理を繰り返して、全行処理させたい」時があります。そのためにはRC形式での指定が便利です。
Web上にあまりそういったことが載っていなかったので、メモ

IT業界にいると、Key-Valueストアって便利だよねとかXMLオブジェクトでXPathで値を指定っていう世界に(否が応でも)慣れていくんですが、世の中そういう世界だけではありません。まだまだExcelの台帳管理とかは生きています。

そうしないと自分しか処理ができない世界が待っていて、おちおち休みも取れなくなります。不在の間派遣さんに「このExcelに書いておいてくれれば良いから」と言って任せることができるのは小規模な事務量の世界ではまだ有効です。

と、いうことでExcelの台帳をサーバが読みに行ってサーバ自身のアクセス権を処理するScriptを組むことにしました。
Excelをハンドリングするところから、読み取り、書き込みあたりまでを実際に試していきます。

PowerShellからExcelを起動する

ExcelをPowerShellから操作するには、PowerShell内のオブジェクトとして掴むことが必要です。

単純に掴むだけであれば、-ComObjectとしてExcelを指定すると(別スレッドで)新しくExcelが起動します。

既存のExcel.exeプロセスがあれば、下記の命令でもオブジェクトを作る事ができます。

これ環境によっては掴めないようで、色々試したところ、こんな感じ。

  • 他のユーザが実行しているExcelプロセスは掴めない(当然)
    「管理者として実行」したPowerShellは、「管理者として実行」しているExcelプロセスしか掴めない(!!)同じユーザのプロセスであっても一般権限で動いているExcelは掴めない。
    同じPowerShellのプロセスが起動したExcelプロセスは掴むことができる
  • New-Objectで作成した場合、ExcelAddinが無効の状態で起動する。

今回の場合、Excelを読み込んだ後システムへの変更処理を組み込む予定なのでPowerShellを管理者権限で動かす必要があります。普段のオペレーションでExcelを管理者権限で動かすなんて危険なので止しておきたい。
If文かtry~catch句を使って掴めなかった場合の処理をしておいた方が良さそうです。

ちなみに、掴めなかった時のエラーはこんな感じで返ってきます。

0x800401E3のコードで検索すると「Running Object Table (ROT)」アプリケーションが無いという内容で「起動からROTに載るまでは少しタイムラグがある」というFAQが見つかります。このExceptionは、Excelが全く起動していない時でも同じコードで返ってきます。

また、出来上がったオブジェクトのクラスが異なる事があります。

起動しているExcelプロセスをPowerShellから掴むとこんな感じ。

InteropServices.MarshalクラスからGetすると必ずSystem.__ComObjectになるかというとそうでもなくて、色々試したところの推定は以下の通りです。

ユーザが起動したExcelがある
→[System.Runtime.InteropServices.Marshal]::GetActiveObject(“Excel.Application”)でオブジェクトをgetする
→netFrameworkのWrapperで包んでSystem.__ComObjectになる。

New-ObjectでPowerShellからExcelプロセスを起動する
→Microsoft.Office.Interop.Excel.ApplicationClassのオブジェクトができる
→[System.Runtime.InteropServices.Marshal]::GetActiveObject(“Excel.Application”)でExcelプロセスを掴む
→Microsoft.Office.Interop.Excel.ApplicationClassのオブジェクトになる。

(参考)
https://docs.microsoft.com/ja-jp/dotnet/framework/interop/runtime-callable-wrapper

出来上がったオブジェクトによって使えるMethodが異なるのですが、PowerShellで外側から弄ってやろうという程度だと実用上の差はないと思います。

興味がある方は、下記を見てください。

Eventが拾えるかとかそういう違いなので、そこまでしたいのであればC#とかVBとかでコンパイルしたものを実行した方が良いのでは。

既存のExcelブックを開く

次に、Excelブックを。開いていない場合は、Workbooks.Openメソッドで開けます。下記の例はScriptと同じフォルダにテスト用ブックを作った前提の命令です。

既に開いているExcelの中に目的のブックがある場合は、Workbooksコレクションの中に含まれているのでそれを指定してやれば掴むことができます。

これもIf文かtry~catch句使ってハンドリングしましょう。

Excelブックの中身を読み取る

Excelを開いてセルを読み込もうとすると、Activeシートから読み込もうとするのがデフォなので、きちんとシート名を指定したほうがいいです。
下記のようにすると、A1セルの内容を$Dataに格納します。

Excelブックに書き込む

Cells自体はオブジェクトですが、値を代入することでSetメソッドが実行されます。

式の場合はRC3と入れてやるとRC列3行目を参照するようなので、同じ行でもR[0]C[-2]といったようにRの後ろに数字を入れてやりましょう。

RC形式でRange指定する

Rangeオブジェクトは$Sheet.Range(“A1:D3”)と書けばA1:D3範囲を選択してくれます。
RC形式だと範囲の開始セルと終了セルをオブジェクトとして指定する必要があります。
1列目から10列目を範囲指定してデータを消去(データ形式は保持)するのだとこんな感じ。

1行目にあるヘッダを読み込んで列番号を取得する

Excelの先頭行にヘッダを設定しているのはよくあると思います。
ところが、Excelだと気軽に列挿入されてしまいます。
列番号指定より列名指定で処理した方が楽だろう、ということで変数に入れてしまいます。

こうしてやると、行数と列名で処理できるので、あとはLoop処理して1行ごとに処理をしていけます。

PowerShellでExcelをRC形式で呼び出し繰り返し実行する” に対して1件のコメントがあります。

コメントを残す

メールアドレスが公開されることはありません。

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