■はじめに
excel2000で、excel2007のファイルを開く際、関連付けられたプログラムで開きたかったので、
openメソッドではなく、runメソッドを使って開くことにしました。
ファイルは狙い通りに関連付けられたプログラムで開けたのですが、ファイルを開いてから、次の
処理に向かう際に下記異常があります。
1つめ・・・runメソッドの第2パラメータ(ウインドウの表示方法)を指定しても効かない
2つめ・・・runメソッドの第3パラメータ(起動したプログラムの終了を待つかどうかを指定)をtrueにすると
ずっと、マクロが実行中になってしまう
■相談したいこと
上記の問題点を解決するために、どのような修正を加えればいいか、教えてもらえないでしょうか?
下記、プロシージャでは、runがずっと実行中になってしまいます。第3パラメータを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ベストアンサー
- 回答日時:
No.2,4です。
その後いくつか進展がありましたのでまとめて回答いたします。
長文失礼致します。
1.Workbooks.Openが使えない
Excel2000を使う機会があったのでその際この件について試してみたのですが、質問者様が書かれたとおりExcel2000で互換機能パックを使ってxlsxファイルを開く場合はWorkbooks.Openが使えないのですね。今更ながら理解しました。
しかも、ブック名が元のファイルとは異なり"Xl"に数値7桁が続き、拡張子が".xls"になるのですね。
2.No.4の回答は要修正
その際、No.4の回答のプログラムについても検証してみたのですが、うまく動作しませんでした。時間がなかったので修正して動作させることは出来なかったのですが、修正すべき点が2つあることがわかりました。
要修正その1.ブックを開き終わったかどうかの確認
上記のとおりブック名が元のファイルとは違ったものになります。しかもどういう名前になるかは開くまでわかりません。そのため、開き終わったかどうかについては、簡単にはブックの数が増えたかどうか、手間をかけるなら新たに増えたブックの名前が上記の規則に合っているかどうかで確認するのがよさそうです。
要修正2その2.VBScriptからのVBAの起動
外部からExcelのVBAを起動する処理で、No.4ではブック名とマクロ名を"!"をはさんでつなげて指定しました。Excel2003ではブック名まで指定する方がマクロ名だけの場合よりもトラブルが少ないようなのでこのようにしました。しかし、Excel2000ではマクロ名のみの指定しか許されないようです。つまり、VBScriptの実質的最終行の「TWBn & "!After_Book_Open2"」は「"After_Book_Open2"」に変更しなければならないようです。
3.別法(≒質問文+No.3)
他の方の回答ですがNo.3の回答を見て、質問文の方法と組み合わせればNo.4よりは扱いやすい方法ができそうだと思いました。まだ案のみで全く形になっていないのですが。
考え方としては、No.3の方法の通りExcelを新たに起動してそこでxlsxファイルを開きます。その新たなExcelではVBAが動作していないのでうまく開くはずですし、元のExcelではファイルを開かないのでNo.4とは異なりVBAを停止せずにそのまま動かし続ければよいのです。
コードの書き方としては、質問者様が書かれたコードを元にして、Runの前にCreateObjectで新たなExcelを起動し、Runでは終了を待たず(False指定)、ブックを開き終わったかどうかを判別する処理を入れればよいと思います。なお、関連付けを使って開くと後に述べるとおり新たなExcelが可視になってしまうでしょうから、不可視にしたいならそのような処理を入れることになると思います。
ただ、Excelが複数起動している状態で関連付けを使って外部から開くという点でトリッキーな面があり、安定的に新たなExcelの方で開いてくれるかどうかという不安は残ります。
4.完全に不可視にはできそうもない
質問者様のNo.1への補足で「こっそり」というのがありましたが、今のところのこちらの見解としては、完全に不可視にはできそうもありません。
まず、互換機能パック使用時の「ファイルを変換しています (キャンセル)」のダイアログボックスは必ず出てしまうようです。
それと、質問者様の疑問の1つである関連付けを使った場合のウインドウの制御ですが、これまで当方で試した限りではすべて前面に出てきてしまいます。Excel以外のアプリケーションも少し試したのですが同様でしたのでWindowsの仕様かもしれません。適宜不可視にするとしても、一瞬見える可能性は排除できないと思います。
もしかしたら上記3.の方法で、Runの際にxlsxファイルを単独ではなくEXCEL.EXEの引数として指定すれば不可視のままで開くのかもしれませんが、それがうまくいったとしてもそのあとExcelあるいはブックのオブジェクトを得るところが難しそうです。
5.No.4の解説追加
No.4の回答ですが、解説がかなり抜けてしまいました。
上記のとおりExcel2000では要修正な部分があるのですが、ここではそれには触れずにNo.4の回答のままで書かせていただきます。
まず2つに分けたVBAの前半部分(主要部分)ですが、外部プログラム起動後はすぐに終了し、VBAが動作していない状態となります。そこでVBScriptで書いた外部プログラムで関連づけを使って開きます。
その外部プログラムですが、ブックを開く動作の完了を知るためにブックのオブジェクトを得ています。
方法は、まずExcelのオブジェクトをgetobjectで得て、そこからブックのオブジェクトをたどっています。
本来ならブックに対し直接getobjectしたいところですが、互換機能パックを使って開いた.xlsxのブックはgetobjectすると閉じてしまうという現象が見られたため仕方なく今の手順にしました。そのため、Excelが複数起動している場合にうまく動作しない可能性がありますが、そもそも関連付けを使っているので、必ずExcelが1つだけ起動している状態で使用すべきであり、そういう条件では大丈夫なはずです。
Excelのオブジェクトを得る部分もブックのオブジェクトを得る部分も、エラートラップを用いたループで待つ処理を行っています。
Excelのオブジェクトを得る部分は、テストした限りでは必ず1回で取得成功となったのでループは不要かもしれませんが、以前にAccessでループが必要だった経験から念のため今回のExcelでもループを使用しています。
ブックのオブジェクトを得る部分ですが、ブックを開く処理が終わる前にオブジェクトを取得しようとするとエラーになるので、これを利用してブックを開く動作の完了を検知しています。そのため、エラートラップを用いたループで待つ処理は必須です。待ち時間は100msが600回で1分としていますが、大きなブックを開く場合など待ち時間が不足することが予想されるなら増やさなければなりません。
ご質問等あれば補足ください。
ご返事遅くなり、申し訳ございません。
大変、ご丁寧親切にお答え頂き、ありがとうございました。
しかし、私の理解レベルをはるかに超えた内容になってきてしまい、考えてしまい時間がかかってしまいました。そして、完全に理解するには至りませんでした。
まは下記にも記載させていただきましたが、近日、来年度にウインドウズ7にアップグレードする事が、決まりましたので、そこまで手動で切り替えをすることにいたしました。
今回、回答者様から頂けたアドバイスから学べたものは多かったです。ありがとうございました。
No.4
- 回答日時:
No.2です。
「トリッキーな処理」を解説いたします。
まず、Excel VBAはファイルオープンまでと後処理の2つに分けます。
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ここから(プロシージャ2分割)
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オブジェクトを得る・5秒待っても得られなかったらエラー
'(待つ処理は不要かもしれない。不要ならgetobjectの1行でよい)
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オブジェクトを得る
'1分待っても得られなかったらエラー(待つ処理は必須)
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
- 回答日時:
こんにちは。
#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が二重になっているはずですから、片方を終了させてください。
ご返事遅くなり、申し訳ございません。
私の理解レベルを超えた内容になってきてしまい、考えてしまい時間がかかってしまいました。
近日、来年度にウインドウズ7にアップグレードする事が、決まりましたので、そこまで手動で切り替えをすることにいたしました。
今回、回答者様から頂けたアドバイスから学べたものは多かったです。ありがとうございました。
No.2
- 回答日時:
まず1つめのrunメソッドの第2パラメータですが、どうやらデータファイルのみの指定でWscript.ShellのRunメソッドを行うと第2パラメータを指定しても効かないものが多いようです。
それに対し、プログラムとデータファイルの両方を指定すれば、たいていは効くようです。質問者様は関連付けを使うためにデータファイルのみでRunメソッドを行っているため、これについてはおそらく解決することができないと思います。しかし、ウインドウの状態などはファイルが開いてから設定しなおせばいいのではないでしょうか。
次に2つめの件ですが、Excel VBAでExcelのファイルを開くためにWscript.ShellのRunメソッドを使うことに無理があるような気がします。推定ですが、ExcelはVBA実行中には外部からの要求によるファイルオープンがうまく処理できないのではないでしょうか。
素直な解決法は、質問者様も認識されているとは思いますが、Workbooks.OpenなどExcelから開くことです。
でも、どうしても関連付けを使わなければならないなら、かなりトリッキーになりますが、Excel VBAからExcelのファイルを開くための外部プログラムを起動してVBAは直ちに終了し、外部プログラムはExcelのファイルを開いたらその後の処理のためのExcel VBAを実行する、という方法がありそうです。
この方法についてもっと解説をご希望であれば補足ください。
queuerev2様
ご返事遅れて申し訳ございません。回答ありがとうございました。
おっしゃるとおり、ウインドウの状態は、只 最小化表示して、非アクティブにするだけなので、開いてから設定すればいいのですが、たったこれだけの事が、この処理を自動で行うことが出来ず、手作業で行っています。
(理由)どんな記述をしても、runでファイルを開く処理をして、終了してしまうため。
もし、可能であれば、このウインドウを最小化して、非アクティブにする記述を教えていただければ幸いです。
また、最後のトリッキーな処理と仰られる説明部分は明確に理解出来ませんでした。もし差し支えなければ、どういったものか、あわせて教えていただければ、幸いです。
いろいろとご無理申し上げてすみません。
No.1
- 回答日時:
こんにちは。
失礼かもしれませんが、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というのは、マクロを載せたブックのことで、それが働かないということですか?
通常、ブックを開けば、開いたブックが、前面に出てくるはずです。
この回答への補足
ご返事遅くなり、申し訳ございません。回答ありがとうございます。
私の説明不足ですみません。精一杯補足させていただきます。
ここで 操作したいPCにはウインドウズ2007はインストールされておらず、xlsxのヴューワーソフトがあります。
ネットワーク上に、xlsxが保管されており、そのビューワーソフトで開くと、自動的にxlsに変換されて、excelが自動で立ち上がって、excelで見られるようになります。
そのため、普通にopenメソッドで開くとグチャグチャな記号文字となったexcelファイルが開かれるため、runメソッドのプログラムの関連付けで開く必要がどうしてもありました。
しかし、この場合、なぜかrunメソッドのパラメータが効きません。
そして、エクセルでそのxlsxファイルをこっそりと開いて、データを参照して取り込んで、こっそりとそのxlsxファイルを閉じたい
という次第です。自分の実力に見合わず、高度なことをやろうとしすぎているのは重々承知しておりますが、なにかアドバイスを頂ければ幸いです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) エクセルのマクロを使ってメールを送る方法について教えてください 2 2022/03/29 01:36
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- Visual Basic(VBA) エクセルのマクロについて教えてください。 4 2023/07/03 09:11
- Visual Basic(VBA) VBAが止まります。 2 2022/09/02 14:02
- Visual Basic(VBA) 複数のcsvファイルをExcelに一括変換したい 2 2023/03/03 12:44
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) 【VBA】印刷マクロのループ処理が反映されません 3 2022/08/09 02:15
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Visual Basic(VBA) Excel-VBAでのファイルの開き方 4 2023/02/14 11:01
- Excel(エクセル) Excel VBAどこが間違ってますか? 4 2023/07/17 10:04
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBAで保存しないで閉じると空の...
-
【Excel VBA】マクロでExcel自...
-
エクセル関数>参照ファイル名...
-
エクセルのvbaにて thisworkboo...
-
【Excel】マクロの保存先について
-
エクセルVBAで作成した別ブック...
-
ファイル名変更後も、マクロを...
-
複数のExcelファイルの印刷設定...
-
エクセル;相対パスを絶対パスへ...
-
個人用マクロブックが消えました
-
EXcelのマクロで相対パスでファ...
-
EXCELのボタンによるマクロの登...
-
【マクロ】マクロが保存されて...
-
マクロの保存先、開いてるすべ...
-
excelで直前に参照していたブッ...
-
ExcelVBAで困っています。
-
マクロ 自動でパスワード及び指...
-
Excelのマクロコードについて教...
-
複数のExcelブックから特定シー...
-
EXCELブックが勝手に開いて困っ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBAで保存しないで閉じると空の...
-
エクセル関数>参照ファイル名...
-
【Excel VBA】マクロでExcel自...
-
エクセル;相対パスを絶対パスへ...
-
マクロの保存先、開いてるすべ...
-
【Excel】マクロの保存先について
-
excelで直前に参照していたブッ...
-
EXcelのマクロで相対パスでファ...
-
エクセルVBAで作成した別ブック...
-
複数のExcelファイルの印刷設定...
-
エクセルのvbaにて thisworkboo...
-
EXCELのボタンによるマクロの登...
-
エクセルVBA 個人用マクロブッ...
-
EXCELマクロで、開いてはいるが...
-
EXCELブックが勝手に開いて困っ...
-
エクセルで未保存に対するメッ...
-
[フィルターオプションの設定]...
-
【Excel】特定セルの内容をテキ...
-
ファイル名変更後も、マクロを...
-
名前の変わるブックをアクティ...
おすすめ情報