VBSでEXCELのXLSTARTにあるマクロを実行するには

質問

VBSを実行したときに既にEXCELを開いている場合にはFor Each wb In Workbooksを使ってXLSTARTフォルダにあるファイルのVBAマクロを実行できます。しかし、EXCELを開いていない場合にはCreateObject("Excel.Application")でEXCELを開いてもXLSTARTフォルダにあるファイルそのものが見つかりません。VBSを使わず、プログラムファイルからEXCELを開く場合にはXLSTARTフォルダにあるファイルも問題もなく開きます。どうすればEXCELを開いていない場合でもVBSでEXCELのXLSTARTにあるマクロを実行できるでしょうか？
因みにXLSTARTのフォルダパスは不特定多数の環境（OSはWindows95-XP、EXCELは97-2007の組合せ）で特定されていないものとします。さらにツール、オプションの全般タブの「起動時に全てのファイルを開くフォルダ」にはXLSTARTが指定されているものとします。

回答No.7

SHIMAPEE

回答6の補足へのアドバイスです。

書かれたコードはVBAでは正しいのかもしれませんが、WSH VBScriptの文法とは違いますので動きません。WSHなどで検索して文法を確認して下さい。

詳しくはないのですが、VBAとして見てもこのコードには疑問があります。

まず、Sleepの代わりとして何かのファイルをOpen/Closeするのは、ＯＳのリソースを食いすぎるのでは。

もしやこのコードは別のプログラムのファイル作成か、ファイルの使用の終了を待つためのものでは。そう考えれば少しは理解できる気がします。しかし、そうだとすれば本件の目的には合いません。

また、エラーの際にすぐ次のループに入るのは他のプログラムの実行を妨げるのでは。少し待ってからＯＳに制御を渡し（VBAでしたらSleepしてDoEventsでしょうか。どちらか一方でもよさそうですが）、やり直すのがよいのでは。

また、一般論としては他のプログラムを永遠に待たないように限度を設けるのがよいのでは。

当初の質問の回答のネタは出尽くしたと思いますので、このＱ＆Ａもそろそろ閉じた方がよろしいでしょう。

お礼

ご回答有難うございました。

WSHについてはVBAよりももっと知識が無いものですからお聞きしました。
おっしゃる通りにネタは出尽くしたようですので閉じさせていただきます。

回答No.6

SHIMAPEE

回答5の補足へのアドバイスというか私の勉強です。

ファイル名に空白がある場合、二重引用符で囲む必要がありました。

更に試行して、二例を作りました。ダイアログの方で十分でシステムへの負担が少ないと思いますが、リトライする方も技術的に勉強になりました。まだ何か考え落ちがあるかもしれません。

-----
const ExcelFileName = "C:\test folder\test.xls"
const MB_OKCANCEL=1
const MB_ICONQUESTION=32
const MB_TOPMOST=&H40000
const IDOK=1
Set WShell = CreateObject("WScript.Shell")
WShell.Run("""" & ExcelFileName & """")
if WShell.PopUp("マクロを実行しますか？" & vbLf & vbLf & _
"Excelが開くのを待って応答して下さい。",0,"確認", _
MB_OKCANCEL+MB_ICONQUESTION+MB_TOPMOST)<>IDOK then
WScript.Quit 'ダイアログで時間かせぎした方が実用的かもしれません
end if
Set objExcel = GetObject(ExcelFileName)
WScript.Sleep 1000 '1秒くらいでよさそうです
objExcel.Application.Run "PERSONAL.XLS!CommonMacro1"
-----
const ExcelFileName = "C:\test folder\test.xls"
const WaitMSec = 500 '★待ち時間[msec]。なるべく長くすることをお奨めします
const LoopMax = 20 '★最大ループ回数
const MB_ICONEXCLAMATION=48
const MB_TOPMOST=&H40000
Set WShell = CreateObject("WScript.Shell")
WShell.Run("""" & ExcelFileName & """")

On Error Resume Next
LoopCount = 0
do
LoopCount = LoopCount + 1
WScript.Sleep WaitMSec
Set objExcel = Nothing
'実行中のExcelを探す
Set objExcel = GetObject( ,"Excel.Application")
FullName = objExcel.ActiveWorkBook.FullName
'LoopMax回ループするか目的のファイル名のExcelが開かれて見つかるまで
loop until (LoopCount=>LoopMax) or (FullName=ExcelFileName)
On Error Goto 0

if LoopCount=>LoopMax then
WShell.PopUp "別のExcelファイルが開かれているようです。" & vbLf & vbLf & _
"全て終了させてから再実行して下さい。", _
0,"エラー",MB_ICONEXCLAMATION+MB_TOPMOST
WScript.Quit
end if

WScript.Sleep 1000 '1秒固定で
objExcel.Application.Run "PERSONAL.XLS!CommonMacro1"
WScript.Echo LoopCount
-----

補足

WScript.Sleep Waitの部分をやめるための工夫です。
VBAで入出力をする場合には下記のようにOpen Path For Randam As #1
が使えるのですが、WSHではエラーになって使えないようです。

Ck = 0
do
Ck = Ck + 1
Err.Clear
On Error Resume Next
Open Path For Randam As #1
Close #1
On Error Goto 0
loop until Err.Number > 0

どのようにすればよいのでしょうか？

回答No.5

SHIMAPEE

回答4のお礼へのアドバイスというか私の勉強です。

試行してみて、だいたい下のコードで私の環境では動くようになりました。ctr, ctr2は動作確認のためのカウンタです。

WindowsXPでは初回のGetObject前に多めに待たないと別のExcelが起動してしまったりします。Vistaでは1msでもＯＫでした。

GetObject成功後にも待ちが必要なことがあります。objExcel.Application.Readyのコードを入れてはみましたが全く効果が無いようです。ダイアログで時間かせぎした方が実用的かもしれません、というところです。

-----
ExcelFileName = "C:\Test\Test.xls"
Set WShell = CreateObject("WScript.Shell")
WShell.Run(ExcelFileName)

On Error Resume Next
ctr = 0
do
ctr = ctr + 1
WScript.Sleep 500
Err.Clear
Set objExcel = GetObject(ExcelFileName).Application
loop until Err.Number=0
On Error Goto 0

ctr2 = 0
do
ctr2 = ctr2 + 1
WScript.Sleep 500
loop until objExcel.Application.Ready=True 'これの効果は無いようです

if WShell.PopUp("マクロを実行しますか？",0,"確認",1+32)<>1 then
WScript.Quit 'ダイアログで時間かせぎした方が実用的かもしれません
end if

objExcel.Application.Run "PERSONAL.XLS!CommonMacro1"
WScript.Echo ctr & ", " & ctr2
-----

補足

毎度ご回答戴き有難うございます。

(1) WShell.Run(ExcelFileName)で動くと言うのがよく判りません。
私の使っているPC（WindowsXP、EXCEL2003)では以下にしないと動きません。
WShell.Run "Excel.exe """ & "C:\Documents and Settings\ユーザID\デスクトップ\Test.xls" & """", 0
(2)「ctr & ", " & ctr2」は「1,1」で最初からErr.Number=0、Application.Ready=Trueのようです。
しかし、「使用中のファイル」ダイヤログが表示され、「PERSONALは使用中のためロックされています」「[読み取り専用]で開くか、または読み取り専用で開き、ほかの人がファイルの使用を終了したときに通知を受け取るには[通知]を選択する」が出ます。
この表示をなくすには下記に修正してWaitの値をを3400以上にしないとなくなりません。
WShell.Run "Excel.exe """ & ExcelFileName & """", 0 'Excelファイルを開く
WScript.Sleep Wait
if WShell.PopUp("マクロを実行しますか？",0,"確認",1+32)<>1 then
WScript.Quit 'ダイアログで時間かせぎした方が実用的かもしれません
end if
これは経験値ですが、これを違うプログラムにして自動的に表示されないようにする方法があれば教えてください。

回答No.4

SHIMAPEE

回答3の補足へのアドバイスというか私の勉強です。

あまり試していないのですが下のようなことができることがわかりました。

-----
Set WShell = CreateObject("WScript.Shell")
WShell.Run "C:\Test\Test.xls" 'Excelファイルを開く
WScript.Sleep 3000 '表示されるまで待つように調整
Set objExcel = GetObject("C:\Test\Test.xls")
objExcel.Application.Run "PERSONAL.XLS!CommonMacro1"
-----

お礼

お世話様でございます。

お蔭様で、頂いたプログラムについて下記のように一部修正を行い実現できるようになりました。

Set WShell = CreateObject("WScript.Shell")
WShell.Run "Excel.exe """ & "C:\Documents and Settings\ユーザID\デスクトップ\Test.xls" & """", 0 'Excelファイルを開く
WScript.Sleep 3000 '"表示されるまで待つように調整"
Set objExcel = GetObject("C:\Documents and Settings\ユーザID\デスクトップ\Test.xls")
objExcel.Application.Run "PERSONAL.XLS!Macro"
Set objExcel = Nothing

WScript.Sleep 3000についてはスマートに下記でやろうとしましたがだめでした。
With objExcel
While .Busy Or .ReadyState <> 4
Wend
End With
基本的にほゞ目的を達成できたのですが上記についてもう少しアドバイス頂けたらと思います。

回答No.3

SHIMAPEE

回答2の補足へのアドバイスです。

目的に合うかどうかわかりませんし、普通にExcelを開いたときと動作は異なりますが、AltStartupPath（存在すれば）, StartupPath, Path下の順番で、いずれかのPERSONAL.XLSファイルを開くスクリプト例を書いてみました。必要とあらばファイル名が重複しない限りパス内のファイルの全てを検索して開くこともできます。

-----
Set Fs = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
if (objExcel.AltStartupPath<>"") and _
(Fs.FileExists(objExcel.AltStartupPath & "\PERSONAL.XLS")) then
MacroFileName = objExcel.AltStartupPath & "\PERSONAL.XLS"
elseif Fs.FileExists(objExcel.StartupPath & "\PERSONAL.XLS") then
MacroFileName = objExcel.StartupPath & "\PERSONAL.XLS"
elseif Fs.FileExists(objExcel.Path & "\XLSTART\PERSONAL.XLS") then
MacroFileName = objExcel.Path & "\XLSTART\PERSONAL.XLS"
else
WScript.Echo "マクロファイルが見つかりませんでした。終了します。"
objExcel.Quit
WScript.Quit
end if
WScript.Echo "MacroFileName = " & MacroFileName
objExcel.Visible = True
objExcel.Workbooks.Open MacroFileName
objExcel.Workbooks.Open "C:\Test\MacroTest.xls"
objExcel.Application.Run "PERSONAL.XLS!CommonMacro1"
objExcel.Application.Run "BookMacro1"
-----

と書いていて思いついたのですが、QWERIO0918さんのやりたいことが不特定多数の環境でWSHを使って自動実行することであれば、VBSファイルと、必要なマクロが入ったXLSファイルを一緒に配布して使えば確実なのではないでしょうか。

そうすれば各PCのマクロファイルの位置やファイル名、マクロ名の違いを気にせずスクリプトを記述できます。

補足

回答番号：No.3補足です。

AltStartupPathは「起動時に全てのファイルを開くフォルダ」に入力したパスですので当てに出来ません。
つまり、全ユーザーに共通の起動(XLStart)フォルダ、例えばEXCEL2003であれば「C:\Program Files\Microsoft Office\Office11\XLSTART」はご回答頂いたパスのどこにも出てこない可能性がありますので"マクロファイルが見つかりませんでした。終了します。"となり得ます。

また、「必要なマクロが入ったXLSファイルを一緒に配布して使えば確実なのではないでしょうか。」については、全くその通りなのですがその特定XLSファイルはどうしても分離せざるを得ない状況下におかれています。

LotusNotesの式にはExecuteコマンドというものがあり、特定のエクセルファイルを起動するだけで自動的にXLSTARTにあるファイルも起動します。同じようにCreateObjectを使用しなくても自動的にXLSTARTのファイルも起動されるような別のコマンドというものはないのでしょうか？

回答No.2

SHIMAPEE

回答1の補足へのアドバイスです。

Bookを開いたりBookのマクロを実行したりしているのは私の勉強のためですので無視して下さい。

Excelの何かの情報をWSHから参照できるかどうかはWSHの機能によるのではなく、Excelがそれを公開しているかどうかにかかっています。

「起動時に全てのファイルを開くフォルダ」は、たぶんExcelのApplicationオブジェクトのプロパティにあるだろうと思い、探しましたらありました。AltStartupPathプロパティです。

また、全ユーザーに共通の起動(XLStart)フォルダはPathプロパティの下だと思われます。

下のスクリプトはApplicationオブジェクトのPath関連のプロパティを表示します。
WindowsXP + Excel2002とVista + Excel2007で試しました。

-----
Set objExcel = CreateObject("Excel.Application")
s = "StartupPath = " & objExcel.StartupPath & vbCrLf
s = s & "AltStartupPath = " & objExcel.AltStartupPath & vbCrLf
s = s & "DefaultFilePath = " & objExcel.DefaultFilePath & vbCrLf
s = s & "LibraryPath = " & objExcel.LibraryPath & vbCrLf
s = s & "Path = " & objExcel.Path & vbCrLf
s = s & "UserLibraryPath = " & objExcel.UserLibraryPath
WScript.Echo s
objExcel.Quit
-----

補足

お世話様です。
返事が遅れて申し訳ありませんでした。

ご回答の内容は頂いたパスのどれかに入るはずということだと思います。
然しながら、私のやりたかったことは不特定多数のユーザーに対してXLSTARTがどこにあっても一度エクセルを開けば、同時にXLSTARTフォルダのファイルも開くだろうからFor Each wb In Workbooksを使ってXLSTARTフォルダのパスも判り、ここにある特定のファイルのVBAマクロを実行できるはずだと考えました。

しかし、以下のサイトで「CreateObject コマンドを使用した場合、Excel でアドイン（XLSTARTフォルダのファイル）が読み込まれない」ことが判りました。
http://support.microsoft.com/kb/213489/ja

回避策も載っていますが、元々パスを指定しないとできない内容なので事実上無理なことでした。
お教え頂いたAltStartupPathについては「起動時に全てのファイルを開くフォルダ」に入力したパスになるので元々入力していない場合には空白になってしまいます。

もっと、低次元の回避策を考える以外になさそうです。

回答No.1

SHIMAPEE

Excelには詳しくないのですが興味があったので試してみました。参考になりましたら。

XLSTARTにあるマクロはPERSONAL.XLSを開けば実行できました。また、XLSTARTのパスはStartupPathプロパティを参照すればよさそうです。

フォルダ、ファイル、マクロをそれなりに作成し、下のOSとExcelの組み合わせで試しました。

Windows98 + Excel2000
WindowsXP + Excel2002
Vista + Excel2007(ただしXLSTARTのファイル名はPERSONAL.XLSBに書替え)

-----
Set objExcel = CreateObject("Excel.Application")
WScript.Echo objExcel.StartupPath
objExcel.Visible = True
objExcel.Workbooks.Open objExcel.StartupPath & "\PERSONAL.XLS"
objExcel.Workbooks.Open "C:\Test\MacroTest.xls"
objExcel.Application.Run "PERSONAL.XLS!CommonMacro1"
objExcel.Application.Run "BookMacro1"
-----

補足

objExcel.Workbooks.Open "C:\Test\MacroTest.xls"、objExcel.Application.Run "BookMacro1"を何のために追加しているのか良く意味か判りません。
以下で充分だと思います。
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
WScript.Echo objExcel.Application.StartupPath
objExcel.Visible = True
objExcel.Workbooks.Open objExcel.StartupPath & "\PERSONAL.XLS"
objExcel.Application.Run "PERSONAL.XLS!マクロ"
ただ、StartupPathはログインユーザーの起動(XLStart)フォルダですので全ユーザーに共通の起動(XLStart)フォルダを指定していた場合にはファイルが見つからないことになってしまいます。
ツール、オプションの全般タブの「起動時に全てのファイルを開くフォルダ」で指定したパスが取得できるコマンドがWSHにあれば良いのですが？