vba で全てのエクセルファイルを開く処理

質問

お世話になります。
下記のVBAソースで、特定のフォルダにあるエクセルを全て
開く処理をしているのですが、スマートに行えているか
疑問に思いました。

具体的には、「'''''''''時間がかかっている？？」箇所で
無駄なこと・時間がかかっていないか疑問です。

目的としては、「vba で全てのエクセルファイルを開く処理」ですので、
(1)皆さんがされている「vba で全てのエクセルファイルを開く処理」
(2)下記ソースの「'''''''''時間がかかっている？？」は時間がかかっていないか
の、どちらかで、結構ですのでご返事頂ければ有り難いです。

宜しくお願いします。

------------ソース-----------------
ChDir (ThisWorkbook.Path & "\" & フォルダ)
fileName = Dir("*.xls")
Do While fileName <> ""
If fileName <> ThisWorkbook.Name Then
IsBookOpen = False
'''''''''時間がかかっている？？
For Each OpenedBook In Workbooks
If OpenedBook.Name = fileName Then
IsBookOpen = True
Exit For
End If
Next
'''''''''時間がかかっている？？
If IsBookOpen = False Then
Workbooks.Open (fileName)
End If
End If
fileName = Dir()
Loop

回答No.4

cj_mover · ベストアンサー

＃１-３、ｃｊです。＃３訂正です。

> こんな風なシンプルな記述でも、二重起動することがありませんから、
> 問題になることはありません。
この記述は（私の認識の）誤りでした。
問題にならないのは、運用ルールによるものでした。
当方の環境でもやはり問題になる場面はあります。

開いていたブックと同一のブックを再度開こうとする場合、
開いていたブックに未保存のデータがあると、
ーーーーーーーーーーーーーーーーーーーーーーーーー
"ブック名 .xlsは既に開いています。2乗に開くと、これまでの変更内容は破棄されます。ブック名 .xls　を開きますか？"
[はい] [いいえ]
ーーーーーーーーーーーーーーーーーーーーーーーーー
というダイアログが表示され、
実行を中断せざるを得なくなります。

という訳で、ブックを開く前のタイミングで上書き保存してあれば、問題ない、
という但し書きを付けておくべきでした。

または、考えようによっては、予め、自ブック以外のブックをすべて閉じておいて
それから一気に全部開く、などの方法も、検討に値するかも知れません。

以上、訂正でした。失礼しました。

お礼

ありがとうございます！！
元からあったソースを使っていたのですが、疑問がとけました！！！

単純に開く形に変えたいと思います！！

ありがとうございます！！！！

回答No.3

cj_mover

＃１、２、ｃｊです。＃２お礼欄へのレスです。

適切な答えになるか判りませんが、
「二重起動を避ける」
「シンプルな記述と遅い処理を避ける記述」
という２つの観点でお応えします。

例えば私の場合、現在は開発環境も運用環境も共に、64ビット版Excel2010
で、統一されているので、そこに甘んじて簡略化を図るなら、
' ' ///
Sub Re8338991b()
　　Const フォルダ As String = "q8338991\"
　　Dim FileName As String

　　ChDir (ThisWorkbook.Path & "\" & フォルダ)
　　FileName = Dir("*.xls")

　　Do While FileName <> ""
　　　　Workbooks.Open FileName
　　　　FileName = Dir()
　　Loop
End Sub
' ' ///
こんな風なシンプルな記述でも、二重起動することがありませんから、
問題になることはありません。
しかし、拡張子が".xls"ということは、
Excel2003以前の環境への互換を配慮するべき、という意味ですから、
二重起動を避ける記述は必要だと思います。
私周辺の昨年までの環境はExcel2000でしたが、この場合は、
確実にブックの二重起動を回避する必要がありました。

＃２お礼欄の２つめのコード「-ネットで見たソース-」について
「全部開く」という場合、
例えば読み取りだけを目的としていて、
二重起動そのものがトラブルの原因にならないような
アプリケーションやファイル形式ならば、
目を瞑って全部開いちゃっても構わないと思います。
どの環境、どんなファイルなら大丈夫なのか、
試しに手作業で二重起動してみれば、
必要かどうかの判断が出来るのではないでしょうか。
ファイルAをふたつ開いて、ファイルA(1)、ファイルA(2)、という状態で、
ファイルA(1)を書き換えて保存したとして（それが許されるアプリケーションだとして）
ファイルA(2)には反映されない処理があることが問題で、
これを上書き保存すれば、酷いことになります。

シンプルに書くことができて、しかも、無駄なく処理が速い、
という記述があれば、それは理想的ですよね。
実践ではそうはならない場面の方が多いと考えた方がいいと思います。
例えば、当初ご提示の
　　Do While fileName <> ""
　　If fileName <> ThisWorkbook.Name Then
についていえば、記述の簡潔さという意味では問題ないのですが、
Dir()関数でファイル名が見つかった回数だけ、
「ThisWorkbookオブジェクトにアクセスする」「.Nameプロパティを取得する」
ということを繰り返していることになります。
これを
　　Dim ThisName As String
　　ThisName = ThisWorkbook.Name
　　Do While fileName <> ""
　　If fileName <> ThisName Then
のように書けば、
「ThisWorkbookオブジェクトにアクセスする」「.Nameプロパティを取得する」
１回で済むことになります。
繰り返し参照する（比較に用いる）（固定的な）値は、予め変数に格納しておく、
というのは、一般論としてプログラミングの基本だと思います。
変数を使うことに慣れた人からすれば変数を使ってくれた方が
可読性が高く感じられる場合が多いようにも思います。
まぁ、ThisWorkbook.Nameの取得自体はそれほど時間をロスするものではありませんが
　　For Each OpenedBook In Workbooks
　　If OpenedBook.Name = fileName Then
　　IsBookOpen = True
　　Exit For
　　End If
　　Next
というループを、Dir()関数でファイル名が見つかった回数だけ、繰り返すのは、
大きく時間をロスすることになります。
なので、予め変数に格納しておいて、ループ内では変数を基準に判別するように書き換えたのが
＃１のSub Re8338991a()です。
この記述について、または、用語について、誤解があるようですが、
ExcelやExcel VBAでの用語としての"配列"は今回、扱っていませんので、一応、念の為。

それから、
> 下記の「ネットで見たソース」は、ファイルを探す処理は
> 下記の形でシンプルだったのですが、
"ファイルを探す処理"自体は、どの記述でも変わりないです。
その点は整理して考えてください。
見つかったファイル名に対して、
必要に応じて、条件分岐する場合、の記述が、
そちらで思うように（ご本人が納得がいくように）書けていない、
ということなのだと思います。

> 組み込まないといけないのでしょうか？
どう書くかの話は置いといて、
二重起動を回避するかどうかは、
どちらかといえばキチンと書くことをお奨めしますが、
最終的には、そちらで判断してください。
環境によって、そもそも書かないといけない場合もありますし、
現在の私のように必要はないけれど、備えとして書く（書くべきと考える）、場合もあります。

以上、答えになっているといいのですが、、、。

回答No.2

cj_mover

＃１、ｃｊです。＃１への追加レス、補足です。

すみません。書き忘れました。

ご提示の
If fileName <> ThisWorkbook.Name Then
についてですが、
Dir()関数でブック名を探すフォルダが
ThisWorkbook.Path & "\" & フォルダ
というように一階層下のフォルダになっていますから、
普通は、ThisWorkbook.Nameにヒットすることは無いようにも思います。
ただ、中には一階層下に同名の別ファイルを置くような使い方もあるでしょうから、
こちらが書いたものも、念の為、合わせて書いています。
不要と判断出来たら、その部分の記述は削除してください。

お礼

ご連絡ありがとうございます。

追加で疑問なのですが、
下記の「ネットで見たソース」は、ファイルを探す処理は
下記の形でシンプルだったのですが、
教えて頂いた配列や、元記載の一度開いたか確認する箇所は
組み込まないといけないのでしょうか？

ネットで見たソースで、不具合なければ、一番処理が
早そうだったので。。。。

---------一度開いたか確認する箇所----------
'''''''''時間がかかっている？？
For Each OpenedBook In Workbooks
If OpenedBook.Name = fileName Then
IsBookOpen = True
Exit For
End If
Next
'''''''''時間がかかっている？？
--------------------------------

----------------ネットで見たソース----------------------
Do While strFileName <> ""
' 検索した１ファイル単位の処理

' 次のファイル名を参照
strFileName = Dir
Loop

回答No.1

cj_mover

こんにちは。

２例挙げておきます。

まずはご提示の方法を踏襲して無駄を削いでみます。
毎回Workbooksを総なめする必要はないですから、
予め、開いているブックの名前をリストにしておきます。
　　sBuf = sBuf & vbLf & wbk.Name
...
　　sBuf = sBuf & vbLf
という記述でラインフィードに区切られ囲まれたブック名
の直線的なリストが文字列変数sBufに、格納されます。
　　　　If InStr(sBuf, vbLf & FileName & vbLf) = 0 Then
FileNameの前後をラインフィードで挟んだ文字列が
sBufに見つからないなら、ブックは開いていない、と。

' ' ///
Sub Re8338991a()
　　Const フォルダ As String = "q8338991\"
　　Dim wbk As Workbook
　　Dim sBuf As String
　　Dim ThisName As String
　　Dim FileName As String

　　ThisName = ThisWorkbook.Name
　　For Each wbk In Workbooks
　　　　If wbk.Name <> ThisName Then sBuf = sBuf & vbLf & wbk.Name
　　Next
　　sBuf = sBuf & vbLf
　　ChDir (ThisWorkbook.Path & "\" & フォルダ)
　　FileName = Dir("*.xls")

Application.ScreenUpdating = False
　　Do While FileName <> ""
　　　　If InStr(sBuf, vbLf & FileName & vbLf) = 0 Then Workbooks.Open FileName
　　　　With Workbooks(FileName)
　　　　　　If .Name <> ThisName Then
　　　　　　　　' ' 処理
　　　　　　　　Debug.Print .Name　　'　　サンプル上の仮の処理
　　　　　　　　.Close　　'　　サンプル上の仮の処理（閉じても良いならひとつずつ閉じる）
　　　　　　End If
　　　　End With
　　　　FileName = Dir()
　　Loop
Application.ScreenUpdating = True
End Sub
' ' ///

' 次に、私がよくやる方法。

' ' ///
Sub Re8338991j()
　　Const フォルダ As String = "q8338991\"
　　Dim sBuf As String
　　Dim ThisName As String
　　Dim FileName As String

　　ThisName = ThisWorkbook.Name
　　ChDir (ThisWorkbook.Path & "\" & フォルダ)
　　FileName = Dir("*.xls")

Application.ScreenUpdating = False
　　Do While FileName <> ""
On Error GoTo NotOpen_
　　　　With Workbooks(FileName)
On Error GoTo 0
　　　　　　If .Name <> ThisName Then
　　　　　　　　' ' 処理
　　　　　　　　Debug.Print .Name
　　　　　　　　.Close
　　　　　　End If
　　　　End With
　　　　FileName = Dir()
　　Loop
Application.ScreenUpdating = True

Exit Sub

NotOpen_:
　　Workbooks.Open FileName
　　Resume
End Sub
' ' ///

処理を速くする、ということだと、
一度に開いているブックは少ない方が有利ではありますので、
全体の設計との兼ね合いで、許されるなら
ひとつずつ開いて閉じることを検討してみるといいかも、です。
後は、Application系の、
EnableEventsとかCalculation等々、遅くなる原因になり易い
Excel側の処理要求を予め抑制しておく、とか。
ExcelバージョンやＯＳ（グラフィック環境）によっては
Application.WindowStateを一時的に最小化するとか、
Applicationの.Leftや.Topを弄って一時的にモニターに表示できないようにする
とか、昔はやってました。

私自身は最近、あまりブックを開くことに拘る必要がなかったので、
ここ数年の事情はあまりよく知りませんが、Excel2010までなら、
或いはExcel2013でも部分的には、まぁ通用する話ではあるでしょう。

以上です。