excel2000 vba のrunメソッド

質問

■はじめに
excel2000で、excel2007のファイルを開く際、関連付けられたプログラムで開きたかったので、
openメソッドではなく、runメソッドを使って開くことにしました。
ファイルは狙い通りに関連付けられたプログラムで開けたのですが、ファイルを開いてから、次の
処理に向かう際に下記異常があります。

１つめ・・・runメソッドの第２パラメータ(ウインドウの表示方法）を指定しても効かない
２つめ・・・runメソッドの第３パラメータ（起動したプログラムの終了を待つかどうかを指定）をtrueにすると
ずっと、マクロが実行中になってしまう

■相談したいこと
上記の問題点を解決するために、どのような修正を加えればいいか、教えてもらえないでしょうか？
下記、プロシージャでは、runがずっと実行中になってしまいます。第３パラメータをfalseにすると、
ファイルは開きますが、その後の処理（ここではThisWorkbook.Activate）が出来ません。

■↓当方で作成したプロシージャ
Sub ファイルを検索して開く()

Dim fname As String
Dim fpass As String
Dim objWShell

Set objWShell = CreateObject("WScript.Shell")

With Application.FileSearch
.NewSearch
.LookIn = "C:\Users\new\Desktop\データベース"
.Filename = Format(Range("A1").Value, "yyyymmdd") & "_" & "*.xlsx"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then

fpass = .FoundFiles(1)
fname = Dir(fpass)

objWShell.Run fpass, 7, True

MsgBox "ファイルを開きました"

Workbooks(fname).Close SaveChanges:=False

Else
MsgBox "対象ファイルはありませんでした"
End If

End With

ThisWorkbook.Activate

End Sub

回答No.5

queuerev2 · ベストアンサー

No.2,4です。
その後いくつか進展がありましたのでまとめて回答いたします。
長文失礼致します。

１．Workbooks.Openが使えない
Excel2000を使う機会があったのでその際この件について試してみたのですが、質問者様が書かれたとおりExcel2000で互換機能パックを使ってxlsxファイルを開く場合はWorkbooks.Openが使えないのですね。今更ながら理解しました。
しかも、ブック名が元のファイルとは異なり"Xl"に数値7桁が続き、拡張子が".xls"になるのですね。

２．No.4の回答は要修正
その際、No.4の回答のプログラムについても検証してみたのですが、うまく動作しませんでした。時間がなかったので修正して動作させることは出来なかったのですが、修正すべき点が２つあることがわかりました。
要修正その１．ブックを開き終わったかどうかの確認
上記のとおりブック名が元のファイルとは違ったものになります。しかもどういう名前になるかは開くまでわかりません。そのため、開き終わったかどうかについては、簡単にはブックの数が増えたかどうか、手間をかけるなら新たに増えたブックの名前が上記の規則に合っているかどうかで確認するのがよさそうです。
要修正２その２．VBScriptからのVBAの起動
外部からExcelのVBAを起動する処理で、No.4ではブック名とマクロ名を"!"をはさんでつなげて指定しました。Excel2003ではブック名まで指定する方がマクロ名だけの場合よりもトラブルが少ないようなのでこのようにしました。しかし、Excel2000ではマクロ名のみの指定しか許されないようです。つまり、VBScriptの実質的最終行の「TWBn & "!After_Book_Open2"」は「"After_Book_Open2"」に変更しなければならないようです。

３．別法（≒質問文＋No.3）
他の方の回答ですがNo.3の回答を見て、質問文の方法と組み合わせればNo.4よりは扱いやすい方法ができそうだと思いました。まだ案のみで全く形になっていないのですが。
考え方としては、No.3の方法の通りExcelを新たに起動してそこでxlsxファイルを開きます。その新たなExcelではVBAが動作していないのでうまく開くはずですし、元のExcelではファイルを開かないのでNo.4とは異なりVBAを停止せずにそのまま動かし続ければよいのです。
コードの書き方としては、質問者様が書かれたコードを元にして、Runの前にCreateObjectで新たなExcelを起動し、Runでは終了を待たず（False指定）、ブックを開き終わったかどうかを判別する処理を入れればよいと思います。なお、関連付けを使って開くと後に述べるとおり新たなExcelが可視になってしまうでしょうから、不可視にしたいならそのような処理を入れることになると思います。
ただ、Excelが複数起動している状態で関連付けを使って外部から開くという点でトリッキーな面があり、安定的に新たなExcelの方で開いてくれるかどうかという不安は残ります。

４．完全に不可視にはできそうもない
質問者様のNo.1への補足で「こっそり」というのがありましたが、今のところのこちらの見解としては、完全に不可視にはできそうもありません。
まず、互換機能パック使用時の「ファイルを変換しています　（キャンセル）」のダイアログボックスは必ず出てしまうようです。
それと、質問者様の疑問の１つである関連付けを使った場合のウインドウの制御ですが、これまで当方で試した限りではすべて前面に出てきてしまいます。Excel以外のアプリケーションも少し試したのですが同様でしたのでWindowsの仕様かもしれません。適宜不可視にするとしても、一瞬見える可能性は排除できないと思います。
もしかしたら上記３．の方法で、Runの際にxlsxファイルを単独ではなくEXCEL.EXEの引数として指定すれば不可視のままで開くのかもしれませんが、それがうまくいったとしてもそのあとExcelあるいはブックのオブジェクトを得るところが難しそうです。

５．No.4の解説追加
No.4の回答ですが、解説がかなり抜けてしまいました。
上記のとおりExcel2000では要修正な部分があるのですが、ここではそれには触れずにNo.4の回答のままで書かせていただきます。
まず２つに分けたVBAの前半部分（主要部分）ですが、外部プログラム起動後はすぐに終了し、VBAが動作していない状態となります。そこでVBScriptで書いた外部プログラムで関連づけを使って開きます。
その外部プログラムですが、ブックを開く動作の完了を知るためにブックのオブジェクトを得ています。
方法は、まずExcelのオブジェクトをgetobjectで得て、そこからブックのオブジェクトをたどっています。
本来ならブックに対し直接getobjectしたいところですが、互換機能パックを使って開いた.xlsxのブックはgetobjectすると閉じてしまうという現象が見られたため仕方なく今の手順にしました。そのため、Excelが複数起動している場合にうまく動作しない可能性がありますが、そもそも関連付けを使っているので、必ずExcelが１つだけ起動している状態で使用すべきであり、そういう条件では大丈夫なはずです。
Excelのオブジェクトを得る部分もブックのオブジェクトを得る部分も、エラートラップを用いたループで待つ処理を行っています。
Excelのオブジェクトを得る部分は、テストした限りでは必ず１回で取得成功となったのでループは不要かもしれませんが、以前にAccessでループが必要だった経験から念のため今回のExcelでもループを使用しています。
ブックのオブジェクトを得る部分ですが、ブックを開く処理が終わる前にオブジェクトを取得しようとするとエラーになるので、これを利用してブックを開く動作の完了を検知しています。そのため、エラートラップを用いたループで待つ処理は必須です。待ち時間は100msが600回で１分としていますが、大きなブックを開く場合など待ち時間が不足することが予想されるなら増やさなければなりません。

ご質問等あれば補足ください。

お礼

ご返事遅くなり、申し訳ございません。

大変、ご丁寧親切にお答え頂き、ありがとうございました。

しかし、私の理解レベルをはるかに超えた内容になってきてしまい、考えてしまい時間がかかってしまいました。そして、完全に理解するには至りませんでした。

まは下記にも記載させていただきましたが、近日、来年度にウインドウズ７にアップグレードする事が、決まりましたので、そこまで手動で切り替えをすることにいたしました。

今回、回答者様から頂けたアドバイスから学べたものは多かったです。ありがとうございました。

回答No.4

queuerev2

No.2です。
「トリッキーな処理」を解説いたします。
まず、Excel VBAはファイルオープンまでと後処理の２つに分けます。
VBAでのファイルオープンは、そのための外部プログラムを起動するだけです。
外部プログラムは、マクロのあるブックと同じフォルダにある"open_extasign.vbs"としました。
残りの部分を後処理とします。
後処理のプロシージャ名は"After_Book_Open2"としました
次に、関連付けを用いてブックを開き、その後VBAを呼び出す外部プログラムを用意します。
今回は、外部プログラムとしてVBScriptを使用しました。
スクリプトのファイル名は上記の通り"open_extasign.vbs"です。
処理内容は、ブックを開き、開く処理が完了するまで待ち、Excel VBAを起動する、というものです。
外部プログラムはVBScriptでなくともCOMオブジェクトが扱えて引数を受け取れるものなら何でもOKだと思います。

詳細は下記のコードをご覧ください。
なお、Excel2003での検証しかしておりませんので、もしかしたらExcel2000で不都合があるかもしれません。うまくいかない場合、あるいはそのたご質問等ある場合は補足お願いします。

No.1,3様の新たな回答が出ていますね。
表には一切出ないようにするにはNo1,3様の回答の方法がいいかもしれません。
この回答の方法ではxlsxファイルが見えてしまいますが、Excelを別途起動しないのでエラーになってもExcelオブジェクトが残りません。

' ********** VBAここから（プロシージャ２分割）
Sub ファイルを検索して開く()

Dim fname As String
Dim fpass As String
Dim objWShell

Set objWShell = CreateObject("WScript.Shell")

With Application.FileSearch
.NewSearch
.LookIn = "C:\Users\new\Desktop\データベース"
.Filename = Format(Range("A1").Value, "yyyymmdd") & "_" & "*.xlsx"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then

fpass = .FoundFiles(1)
'外部プログラムを起動し、終了を待たずに次へ
fname = Dir(fpass)
objWShell.Run """" & ThisWorkbook.Path & "\open_extasign.vbs " & _
""" """ & fpass & """ """ & ThisWorkbook.Name & """", , False

Else
MsgBox "対象ファイルはありませんでした"
End If

End With
End Sub

Sub After_Book_Open2(fname)
ThisWorkbook.Activate
MsgBox "ファイルを開きました" & vbCrLf & fname

Workbooks(Dir(fname)).Close SaveChanges:=False

End Sub
' ********** VBAここまで

' ********** VBScriptここから
'open_extasign.vbs
'Excelのブックのフルパスを受け取ってブックを開きVBAを起動する

'処理開始・開くブックのフルパスとマクロのあるブック名を引数で受ける
set Args = Wscript.Arguments
if args.count>=2 then
book1=Args(0) 'ブックのフルパス
TWBn=Args(1) 'ThisWorkbook.name
else
wscript.quit
end if
'ブックを開く
Set objWShell = CreateObject("WScript.Shell")
objWShell.Run """" & book1 & """", 7, False
'フルパスからファイル名（ブック名）を得る
Set objFS = CreateObject("Scripting.FileSystemObject")
book1fn=objFS.getFileName(book1)
'Excelオブジェクトを得る・５秒待っても得られなかったらエラー
'（待つ処理は不要かもしれない。不要ならgetobjectの１行でよい）
on error resume next
counter=0
do
Err.Clear
wscript.sleep 100
set oExcel=getobject(,"Excel.Application")
counter=counter+1
loop until (err.number = 0) or (counter > 50)
en = Err.Number
on error goto 0
if en>0 then
msgbox "Excelオブジェクト取得に失敗しました" & vbcrlf _
& "Err.Number " & en & "; loop counter " & counter
wscript.quit
end if
'開いたブックのWorkbookオブジェクトを得る
'１分待っても得られなかったらエラー（待つ処理は必須）
on error resume next
counter=0
do
Err.Clear
wscript.sleep 100
set oBook = oExcel.workbooks(book1fn)
counter=counter+1
loop until (err.number = 0) or (counter > 600)
en = Err.Number
on error goto 0
if en>0 then
msgbox "Workbookオブジェクト取得に失敗しました" & vbcrlf _
& "Err.Number " & en & "; loop counter " & counter
wscript.quit
end if
'Excel VBAを起動・開いたブックのフルパスを渡す
call oExcel.Run(TWBn & "!After_Book_Open2", book1)
' ********** VBScriptここまで

回答No.3

WindFaller

こんにちは。

#1の回答者です。少し、考えてみました。

>普通にopenメソッドで開くとグチャグチャな記号文字となったexcelファイルが開かれるため、

互換機能パックでパッチ当てされていなければ、どうしようもありませんし、Excel 2000 で動作がどうなっているのか分かりませんから、Openメソッドで開くことができないなら以下の方法はダメだと思います。

もしダメなら、
>エクセルでそのxlsxファイルをこっそりと開いて、
最小化させる方法は、Run のオプションではなく、Windowを取得して、それを最小化させればよいはずですが、まあ、あまり自分のイメージを追うことは諦めたほうがよさそうです。

'//
Sub InVisibleOpen()
　Dim xlApp As Excel.Application
　Dim fn As String
　Dim wb As Workbook
　Dim i As Long
　Dim j As Long
　Dim k As Long
　i = 1 '行数
　j = 1 '列数
　k = 10 '終了行
　fn = "test1.xlsx"　'フル・パスのほうが良い
　On Error GoTo ErrHandler
　Set xlApp = CreateObject("Excel.Application")
　xlApp.Visible = False
　Set wb = xlApp.Workbooks.Open(fn)
　With wb
　　For i = 1 To k
　　　ActiveSheet.Cells(i, j).Value = .Worksheets("Sheet1").Cells(i, j).Value
　　Next i
　　.Close False
　End With
　xlApp.Quit
ErrHandler:
　If Err.Number > 0 Then
　　MsgBox Err.Number & " : " & Err.Description
　Else
　　Set xlApp = Nothing
　End If
End Sub

'//一応、エラートラップは置きましたが、万が一失敗したら、オブジェクトがメモリに残るはずですが、必ず、タスクマネージャで、プロセス側がEXCEL.EXEが二重になっているはずですから、片方を終了させてください。

お礼

ご返事遅くなり、申し訳ございません。

私の理解レベルを超えた内容になってきてしまい、考えてしまい時間がかかってしまいました。

近日、来年度にウインドウズ７にアップグレードする事が、決まりましたので、そこまで手動で切り替えをすることにいたしました。

今回、回答者様から頂けたアドバイスから学べたものは多かったです。ありがとうございました。

回答No.2

queuerev2

まず1つめのrunメソッドの第２パラメータですが、どうやらデータファイルのみの指定でWscript.ShellのRunメソッドを行うと第2パラメータを指定しても効かないものが多いようです。それに対し、プログラムとデータファイルの両方を指定すれば、たいていは効くようです。
質問者様は関連付けを使うためにデータファイルのみでRunメソッドを行っているため、これについてはおそらく解決することができないと思います。しかし、ウインドウの状態などはファイルが開いてから設定しなおせばいいのではないでしょうか。

次に２つめの件ですが、Excel VBAでExcelのファイルを開くためにWscript.ShellのRunメソッドを使うことに無理があるような気がします。推定ですが、ExcelはVBA実行中には外部からの要求によるファイルオープンがうまく処理できないのではないでしょうか。
素直な解決法は、質問者様も認識されているとは思いますが、Workbooks.OpenなどExcelから開くことです。
でも、どうしても関連付けを使わなければならないなら、かなりトリッキーになりますが、Excel VBAからExcelのファイルを開くための外部プログラムを起動してVBAは直ちに終了し、外部プログラムはExcelのファイルを開いたらその後の処理のためのExcel VBAを実行する、という方法がありそうです。
この方法についてもっと解説をご希望であれば補足ください。

お礼

queuerev2様
ご返事遅れて申し訳ございません。回答ありがとうございました。

おっしゃるとおり、ウインドウの状態は、只 最小化表示して、非アクティブにするだけなので、開いてから設定すればいいのですが、たったこれだけの事が、この処理を自動で行うことが出来ず、手作業で行っています。
(理由）どんな記述をしても、runでファイルを開く処理をして、終了してしまうため。
もし、可能であれば、このウインドウを最小化して、非アクティブにする記述を教えていただければ幸いです。

また、最後のトリッキーな処理と仰られる説明部分は明確に理解出来ませんでした。もし差し支えなければ、どういったものか、あわせて教えていただければ、幸いです。

いろいろとご無理申し上げてすみません。

回答No.1

WindFaller

こんにちは。

失礼かもしれませんが、WScript.Shellを使っているとか、現在では使われなくなった、FileSearchを使っているとか、現在では標準的ではない方法で、内容的にも、特殊な部分が多いので、掲示板で聞いても、回答が付きにくいのではないかと思います。

FileSearch を利用して、下位フォルダまで探して複数のブックを探しているようですが、選ぶのは、そのひとつしか選んでいません。

場所が特定できるなら、Dir 関数でも同じです。

そして、見つけたファイルを、
WScript.Shell　オブジェクトで開くというのも、標準的ではないようです。

見つけたブックは、

Workbooks.Open fpass

で良いはずです。

//コードに関しては、

> objWShell.Run fpass, 7, True

この「7」というのは、「ウィンドウを最小化」という意味だと思います。あえて、開けたブックを見るというなら、5ということになるような気がしますが、つけなくもよかったと思います。

True は、objWShellを待機させて、仕事が終了まで置いておくのですか？
意味が分からないです。

objWShell.Run fpass
だけではいけないのですか？

>ファイルは開きますが、その後の処理（ここではThisWorkbook.Activate）が出来ません。

見つけたブックよりも、現在、実行しているマクロのブックをアクティベートする、という意味です。質問として、こうなると期待していたけれども、こうなってしまった、ということが分かりません。

ThisWorkbook.Activate　のThisWorkbookというのは、マクロを載せたブックのことで、それが働かないということですか？

通常、ブックを開けば、開いたブックが、前面に出てくるはずです。

補足

ご返事遅くなり、申し訳ございません。回答ありがとうございます。

私の説明不足ですみません。精一杯補足させていただきます。

ここで 操作したいＰＣにはウインドウズ2007はインストールされておらず、xlsxのヴューワーソフトがあります。

ネットワーク上に、xlsxが保管されており、そのビューワーソフトで開くと、自動的にxlsに変換されて、excelが自動で立ち上がって、excelで見られるようになります。

そのため、普通にopenメソッドで開くとグチャグチャな記号文字となったexcelファイルが開かれるため、runメソッドのプログラムの関連付けで開く必要がどうしてもありました。

しかし、この場合、なぜかrunメソッドのパラメータが効きません。

そして、エクセルでそのxlsxファイルをこっそりと開いて、データを参照して取り込んで、こっそりとそのxlsxファイルを閉じたい

という次第です。自分の実力に見合わず、高度なことをやろうとしすぎているのは重々承知しておりますが、なにかアドバイスを頂ければ幸いです。