Hey, Scripting Guy!

Scripting Guys が皆さんの質問にお答えします

TechNet コラムへようこそ。このコラムでは、よく寄せられるシステム管理スクリプトに関する質問に Scripting Guys がお答えします。システム管理スクリプトについて質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。

詳細情報

Hey, Scripting Guy! カテゴリ別アーカイブ

Hey, Scripting Guy! 日付別アーカイブ

Hey, Scripting Guy! ダウンロード

Office Excel マクロを複数のブックで実行する方法はありますか

 

Scripting Guy さん、よろしくお願いします。Scripting Guy さんの好みはわかりませんが、私は Microsoft Office Excel マクロが大好きです。今年のバレンタイン デーには、私のコンピューターでカードを受信します。Office Excel マクロを使用すると、信じられないくらいほど大幅に時間を節約できます。ただし、問題が 1 つあります。それは、Office Excel を開いて手動でマクロを実行する必要があることです。普段ならこれは問題ではありませんが、今回は問題になりました。私の無能な上司 (PHB: pointy-headed boss) が、無駄な書類の新しいカバー レターが必要だと決めたのです (文字どおりではなくたとえとしての意味です)。どういうことかと言うと、Office Excel ブックが何百も含まれているフォルダーがあり、PHB はすべてのブックでこのマクロを実行したがっています。マクロ自体は会社のマクロで、すべてのブック内に自動的に存在しています。PHB は私に、すべてのブックを開いて [マクロ] ボタンをクリックし、このマクロを実行するよう求めています。この作業には何時間もかかるでしょう。私が PHB に臨時職員を雇う必要があると伝えると、彼は次のように答えました (引用します)。「急ぐ必要はない。空き時間にやればいいさ。四半期末までに完了してくれれば、問題ないよ」と。ですが、四半期末は来週で、私には、この作業に時間を費やす余裕がありません。手伝っていただけないでしょうか。

- GG

GG さん、こんにちは。

お手伝いしたいのはやまやまですが、GG さんのオフィスに行って、大量の Excel ブックを開き、マクロのボタンをクリックしてマクロを実行するお手伝いをするつもりはありません。私は 15 分もたたないうちに退屈で死んでしまうと思います。私が Scripting Guy になった理由の 1 つは、私がありきたりのことを我慢できない性格で、時間の無駄使いになる退屈な作業が大嫌いなためですからね。ですから、GG さんのオフィスに行ってボタンをクリックするのを手伝う代わりに、スクリプトを作成して差し上げたいと思います。

信じられないかもしれませんが、Office Excel マクロにはスクリプトでアクセスできます。Windows PowerShell を使用して特定の種類のファイルを検索できることは既にわかっているので、フォルダーで Office Excel ブックを検索するスクリプトを作成すれば、後は Office Excel オートメーション モデルを使用して、ブックを開いてマクロを実行できます。すばらしいですよね。

Office Excel オートメーション モデルについては、MSDN のこちらのページ (英語) を参照してください。VBScript を使用して Microsoft Excel を自動化する方法を紹介している以前の Hey, Scripting Guy! 記事については、こちらを参照してください。Office Space アーカイブにも、Office Excel に関して VBScript を使用する例が多数あります。「スクリプト一覧」でも、Office Excel を自動化する例を多数確認することができます。でも、最後にご紹介するものが 1 番お役に立つかもしれません。Community-Submitted Scripts Center では、Office Excel を自動化するスクリプトが非常にたくさん提供されています。この情報の宝庫への直接のリンクはこちらです。Microsoft Office セクションにアクセスしてみてください。Microsoft PowerShell を初めて使用する方は、Windows PowerShell の拠点 (英語) で提供されているリソースを使用すると好調なスタートを切ることができます。

今日のスクリプトは次のとおりです。

PowerShell
$excel = new-object -comobject excel.application 
$excelFiles = Get-ChildItem -Path C:\fso -Include *.xls, *.xlsm -Recurse 
Foreach($file in $excelFiles) 
{ 
 $workbook = $excel.workbooks.open($file.fullname) 
 $worksheet = $workbook.worksheets.item(1) 
 $excel.Run("CreateChart") 
 $workbook.save() 
 $workbook.close() 
} 
$excel.quit()
 

このスクリプトでは、まず、Excel.Application オブジェクトのインスタンスを作成します。これは、Office Excel の自動化を処理する際に使用する主なオブジェクトです。Excel.Application オブジェクトから、自動化に必要な他のすべての項目にアクセスします。Windows PowerShell でオブジェクトを作成するには、New-Object コマンドレットを使用します。このコマンドレットには COM オブジェクトを作成するオプションがあり、このオプションが今回は必要です。これは、VBScript で処理する場合に使用するオブジェクトと同じです。スクリプトでは後で Excel.Application オブジェクトを使用する必要があるので、Excel.Application オブジェクトを $excel という名前の変数に格納します。この処理を実行するコード行を次に示します。

PowerShell
$excel = new-object -comobject excel.application
 

次に、フォルダー内のすべての .xls ファイルと .xlsm ファイルの一覧を取得する必要があります。ファイルの一覧を取得するには、Get-ChildItem コマンドレットを使用します。詳しくない方のために説明すると、.xlsm 拡張子が付けられたファイルは、マクロが有効な Office Excel ブックです。このファイルの種類は、2007 Office system で導入されました。検索を 2 種類のファイルに限定するには、Get_ChildItem コマンドレットで -Include スイッチを指定します。-Include スイッチではファイルの種類の配列を受け取れるので、コンマで区切られた複数のファイルの種類を使用できます。-Recurse パラメーターは、Get-ChildItem コマンドレットにフォルダーの下部構造も処理するよう指示するのに使用します。返されるファイル オブジェクトのコレクションは、$excelFiles 変数に格納します。この処理を実行するコード行を次に示します。

PowerShell
$excelFiles = Get-ChildItem -Path C:\fso -Include *.xls, *.xlsm –Recurse
 

続いて、$excelFiles 変数に格納されたファイルのコレクションを 1 つずつ処理する必要があります。"コレクション" という言葉を聞いたら、必ず Foreach (VBScript の場合は foreach next) を思い浮かべてください。$file 変数は、コレクションのファイルに 1 つずつアクセスできるようにするために使用します。この処理を実行するコード行を次に示します。

PowerShell
Foreach($file in $excelFiles)
 

次は、Office Excel ブックを開く必要があります。そのためには、workbooks コレクションの open メソッドを使用します。workbooks コレクションを取得するには、Excel.Application オブジェクトの workbooks プロパティを使用します。open メソッドを使用する際は、開くブックのパスを指定する必要があります。open メソッドからは workbook オブジェクトが返され、このオブジェクトを次のように $workbook 変数に格納します。

PowerShell
$workbook = $excel.workbooks.open($file.fullname)
 

その後、workbook オブジェクトの worksheets プロパティを使用してワークシートのコレクションを取得します。続いて worksheets オブジェクトの item メソッドを使用して、コレクションの最初のワークシートを返します。返されたワークシートは、$worksheet 変数に格納します。この処理を実行するコード行を次に示します。

PowerShell
$worksheet = $workbook.worksheets.item(1)
 

特定のワークシートを選択したら、Excel.Application オブジェクトの Run メソッドを使用して特定のマクロを実行します。このマクロを次に示します。

 

Run メソッドを使用すると、マクロまたは関数を実行できます。この処理を実行するコード行を次に示します。

PowerShell
$excel.Run("CreateChart")
 

スクリプトを実行するとき、画面には何も出力されません。しかし、Office Excel ブックを開くと、マクロが実行されすてきなグラフが作成されたことがわかります。グラフが作成された Excel ブックを以下に示します。

 

マクロを実行したので、ブックを保存して閉じます。

PowerShell
$workbook.save() 
$workbook.close()
 

引き続き、すべてのブックを処理するまでブックのコレクションをループ処理します。処理が完了したら、quit メソッドを呼び出して、Office Excel プロセスを終了します。

PowerShell
$excel.quit()
 

GG さん、これで完了です。このスクリプトで GG さんの作業が楽になり、仕事での緊張を少しでも緩和できることを心から願っています。残念ながら、無能な上司を普通の人間に変身させるスクリプトは持ち合わせていません。明日もお会いしましょう。それまでの間、しばしのお別れです。

 

Scripting Guys (Ed WilsonCraig Liebendorfer)


ページのトップへ