Hey, Scripting Guy!
Scripting Guys が皆さんの質問にお答えします
TechNet コラムへようこそ。このコラムでは、よく寄せられるシステム管理スクリプトに関する質問に Scripting Guys がお答えします。システム管理スクリプトについて質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。
詳細情報
| • | |
| • | |
| • |
特定のフォルダ内にあるすべての Excel ワークシートを開き、各ワークシートにある指定のマクロを実行する方法はありますか
Scripting Guy さん、よろしくお願いします。ある特定のフォルダをループ処理し、そのフォルダ内のすべての Excel ファイルを開いて、各 Excel ファイルにある指定のマクロを実行する方法はありますか。
-- BR
BR さん、こんにちは。先週の土曜日に開催されたカレッジ フットボールは観戦されましたか。具体的な話をすると、ボイシ州立大学を 24 対 10 で破ったワシントン ハスキーズの試合はご覧になりましたか。この試合で、国内最高記録だったブロンコスの 14 連勝という記録は破られました。ワシントン ハスキーズの試合を観戦した方は、ジェイク ロッカーがハスキーズの最初のタッチダウンを決めた場面はご覧になりましたか。
それをご覧になっていないとしたら、お気の毒ですね。でも、実は、このコラムを執筆している Scripting Guy も見逃しました。おかしな間違い続き (そのときは特におもしろいと感じることはありませんでした) のせいで、このコラムを執筆している Scripting Guy がスタジアムに到着したのは、ロッカーがタッチダウンを決めた直後、ハスキーズがキックして追加点を獲得したときでした。
もちろん、彼は、おもしろくありませんでした。
皆さんがその話について激論したいと思っていることはわかっています。まず、あの日の朝は少々波乱の幕開けだったことについてお話しましょう。Scripting Guy の家族 (および Kyle) が朝食をとるお気に入りのレストランに到着したとき、レストランは少し混雑していたので、席に着くまで少し待たなければなりませんでした。これは、接客の対応がいつもより若干遅かったということでもあります。でも、それはかまいませんでした。というのも、このコラムを執筆している Scripting Guy は、パーク アンド ライドに 11 時 15 分までに到着したいと考えていましたが、Scripting Guy の家族と Kyle は、なんとか 11 時 20 分には到着したのですから。
試合の開始時刻が 12 時 30 分だったことと、ハスキー スタジアムまでは車で 30 分かからないことを考えると、Scripting Guy の家族の行程はかなり順調でした。少なくとも、試合日に運行される臨時高速バスが実際にパーク アンド ライドに来ていたら、そのはずでした。このコラムを執筆している Scripting Guy は、ハスキーズのフットボール試合に 19 年間通い詰めていますが、途中でバスに乗るまでに数分間以上待たされたことはありませんでした。いつもは、乗客を待っているバスが 2 〜 3 台並んでいます。先日は、並んでいるバスはなく、しだいにイライラしてきたハスキーズ ファンは、1 台のバスが到着するまで 25 分間も待たされました。
注 : おかしなことに、20 分の間に 1 台のバスも来ないにもかかわらず、バス会社の代表が、10 分間隔でバスが運行していることを乗客に約束しても何の役にも立ちませんでした。バスは実際に 10 分間隔で運行していたかもしれませんが、ハスキー スタジアムとパーク アンド ライド間を走行していなかったというだけです。 |
ようやくバスが到着し、Scripting Guy の家族 と Kyle は乗車しましたが、バスが発車して高速道路に合流したところで渋滞に巻き込まれてしまいました。シアトルへようこそ。ここは、世界有数の交通渋滞発生地域です。
注 : 世間で思われているのとは裏腹に、このコラムを執筆している Scripting Guy は、Manmelter 3600 ZX のような原子より小さい粒子に粉砕する拳銃を使用して、試合に向かわず、ただ高速道路を渋滞させているすべての車を始末することには賛成しません。しかし、このような人たちが皆逮捕され、投獄されることについて異議はありません。 そうです、編集者は今週休暇を取っています。どうしておわかりになったのですか。 |
うんざりするような徐行運転が続き、バスはようやくハスキー スタジアムの数ブロック先で停車しました。このコラムを執筆している Scripting Guy は、風速、地球の自転、そして 12 時半開始予定の試合がその時間に開始されることはめったにないという事実を考慮して、乗客がキックオフ前に席に着ける可能性はまだわずかに残っていると判断しました。しかし、そのとき、最後の屈辱的な出来事が起きたのです。乗客が急いでスタジアムに向かっているとき、Scripting Guy の息子が突然「お父さん、跳ね橋が上がっていくよ」と言いました。
帆船が下を通りやすいように、Montlake Cut にかかっている跳ね橋がゆっくりとうんざりするような速度で上がっていきました。このコラムを執筆している Scripting Guy が、この出来事にどのように対処したのか、ですって。こう言わせてください。彼が Manmelter 3600 ZX を所持していなくてよかったです。
このコラムを執筆している Scripting Guy は自分のことをだれにも負けない UW フットボールの大ファンだと思っています。その彼が、久々の記録的な大勝利を収めた試合でハスキーズの最初のタッチダウンをなぜ見逃してしまったのだろうと気になっている方のために言っておきますと、混雑したレストラン、"10 分間隔で" 運行するバス、シアトル地域の交通事情、Montlake Cut の跳ね橋などが原因だと考えられます。また、ある特定のフォルダをループ処理し、そのフォルダ内にあるすべての Excel ファイルを開いて、各 Excel ファイルにある指定のマクロを実行する方法が気になっている方のために、次のスクリプトもご紹介します。
strComputer = "tvsfrank"
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colFileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _
& "ResultClass = CIM_DataFile")
Set objExcel = CreateObject("Excel.Application", strComputer)
objExcel.DisplayAlerts = False
For Each objFile In colFileList
If objFile.Extension = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Run("BoldfaceHeadings")
objWorkbook.SaveAs objFile.Name
objWorkbook.Close
End If
Next
objExcel.Quit
鋭いですね。このスクリプトは、いつもの WMI スクリプトと少し異なるように見えますよね。もうご存知だと思いますが、WMI を使用するスクリプト (このスクリプトでは、WMI を使用して C:\Test フォルダにあるすべてのファイルのコレクションを取得します) を記述するときは必ず、いつも決まって最初に次のコード行を記述します。
strComputer = "."
なぜでしょうか。通常、WMI スクリプトでは、スクリプトを実行するコンピュータの名前を strComputer 変数に代入します。確かに、ピリオド (.) はコンピュータ名のようには見えませんが、少なくとも WMI ではローカル コンピュータの省略形という意味を持っています。今回のスクリプトの 1 行目には、「strComputer = "."」と記述しています。これは、追加のコーディングを行わなくても、実際のコンピュータ名に関係なく、ローカル コンピュータに対して実行するスクリプトを用意できるためです。また、リモート コンピュータの名前を strComputer 変数に代入するだけで、同じスクリプトをリモート コンピュータに対しても実行できる、ということもお伝えしておきます。つまり、次のようになります。
strComputer = "atl-ws-01"
ではなぜ、このスクリプトの冒頭に strComputer = "." というコードを記述しなかったのかというと、ローカル コンピュータにある Excel ファイルか、リモート コンピュータにある Excel ファイルに対してマクロを実行できる、煩雑さを最小限に抑えたスクリプトを作成しようとしたためです。既に述べたように、WMI では、ローカル コンピュータを指定する簡単な方法としてピリオドを使用します。しかし、好む好まざるとにかかわらず、Excel ではローカル コンピュータの代わりとしてピリオドを使用しません。Excel のインスタンスを作成するときに、スクリプトを実行するコンピュータを指定できる必要があります。しかし、そのためには、ローカル コンピュータで作業している場合でも、実際のコンピュータ名を指定する必要があります。
注 : 確かに、これは厳密には事実ではありません。コンピュータ名を完全に省略することは可能です。その場合、Excel ではローカル コンピュータが既定値になります。しかし、既に述べたように、煩雑さを最小限に抑えた、リモート コンピュータかローカル コンピュータのどちらに対しても実行できるスクリプトが必要です。そのためには、strComputer 変数に実際のコンピュータ名を代入する方法が最も簡単です。 |
ところで、ここで話している内容が完全にはわからなくても (このコラムで初めてのことではないでしょうが)、あまり心配しないでください。(ローカル コンピュータにあるワークシートを操作する場合でも) strComputer 変数にコンピュータ名を代入するだけで、すべてうまくいきます。
WMI サービスに接続したら、C:\Test フォルダ内にあるすべてのファイルのコレクションを返すために、次のクエリを実行します。
Set colFileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _
& "ResultClass = CIM_DataFile")
これで、マクロの実行を開始する準備ができました。
少なくとも、次のように Excel.Application オブジェクトのインスタンスを作成したら準備完了です。
Set objExcel = CreateObject("Excel.Application", strComputer)
CreateObject メソッドの呼び出しも、いつもと少し異なるように見えることにお気付きかもしれませんね (熱中した Scripting Guys とはまさにこのことですね)。ご覧のとおり、CreateObject メソッドには 2 つのパラメータを渡しています。1 つ目は、Excel.Application (作成するオブジェクトの ProgID) で、2 つ目は strComputer 変数です (通常、CreateObject メソッドの呼び出しには ProgID しか含まれません)。CreateObject メソッドに 2 つのパラメータを渡すと、オブジェクト (この場合は、Excel.Application) の新しいインスタンスが返されます。また、そのインスタンスは 2 番目のパラメータで指定したコンピュータに作成されます。ここでは strComputer 変数に atl-ws-01 という値を代入したので、Excel のインスタンスは atl-ws-01 コンピュータに作成されます。この動作は、atl-ws-01 がローカル コンピュータでもリモート コンピュータでも同じです。
注 : atl-ws-01 に Excel がインストールされていない場合、このスクリプトは失敗するということを付け加えておきます。でもそのことはもうご存知ですよね。 |
Excel のインスタンスを作成したら、次はアプリケーションの DisplayAlerts プロパティを False に設定します。この設定は、Excel に対して、ファイルを保存するときにメッセージやダイアログ ボックスを表示しないことを指示するためのものです。この設定を行わないと、ファイルを保存しようとしたときに、次のようなメッセージが表示されます。
この場所に 'C:\Test\Spreadsheet1.xls' という名前のファイルが既にあります。置き換えますか? |
DisplayAlerts プロパティの値を False に設定すると、このようなメッセージ ボックスは表示されずに、既定の操作が使用されます。この場合の既定の操作とは、[はい] ボタンをクリックすることです。つまり、"はい、このファイルを置き換えます" ということです。
Excel の Visible プロパティに True を設定しなかったことにもお気付きかもしれません。Visible プロパティを True に設定する場合は、スクリプト内の DisplayAlerts プロパティを構成する行の直後に次のコード行を追加します。
objExcel.Visible = False
ただし、Excel が画面上に表示されるのはローカルで実行されている場合だけということに注意してください。Visible プロパティにどんな値を割り当てても、リモート コンピュータに対してこのスクリプトを実行した場合、Excel は非表示のウィンドウで実行されます。スクリプトは問題なく機能しますが、Excel は画面に表示されません。また、リモート コンピュータの画面にも表示されません。
これはセキュリティ対策なので、どうすることもできません。
これで、マクロの実行を開始する準備ができました。まず、返されたコレクション内に含まれるすべてのファイルをループ処理する For Each ループを設定します。このループ内では、まず、Extension プロパティの値を確認して Excel ファイルを操作していることを確認します。Extension プロパティの値には、次のようにピリオドなしの xls という文字列を指定する必要があることに注意してください。
If objFile.Extension = "xls" Then
注 : 2007 Office system では、xlsm というファイル拡張子を確認する必要があります。xlsm は、マクロが有効なワークシートです。 |
Excel ワークシートだと仮定し、次に、Open メソッドと次のコード行を使用してコレクション内の最初のファイルを開きます。
Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
ファイルのパスはわからなくても問題ありません。そのために Name プロパティがあるんです。ファイル内の最初のワークシートにバインドしてから、次のコード行を使用して BoldfaceHeadings という名前のマクロを実行します (言うまでもありませんが、このマクロは各ワークシートに用意されている必要があります)。
objExcel.Run("BoldfaceHeadings")
マクロの実行後、次のように SaveAs メソッドを呼び出して新しく変更したファイルを保存し、Close メソッドを呼び出して、変更したブックを閉じます。
objWorkbook.SaveAs objFile.Name objWorkbook.Close
その後、ループ処理の先頭に戻り、コレクション内の次のファイルについても同じ処理を繰り返します。すべての処理が終わったら、ループ処理を終了し、Quit メソッドを呼び出して Excel のインスタンスを終了し、作業完了です。
BR さん、これでうまくいくでしょう。ちなみに、このコラムを執筆している Scripting Guy のことは心配しないでください。えっ、心配していなかったですって。それは良かったです。なにせ、フットボール試合に遅れることが二度とないように既に対策を講じているのですから。つまり、時間を止めることができるコンピュータの研究に一生懸命取り組んでいます。そうすれば、たとえば 12 時 25 分で時間を止め、スタジアムで自分の席に座ってから再び時間を開始することができます。
このようなコンピュータを作成することが簡単だと言っているわけではありませんが、それでも、土曜日の朝に 30 分早く起床することよりは簡単です。そんな早く起きるなんてあり得ません。
