ExcelVBAのオートメーションエラー

Question

【ExcelVBA】

Excelマクロを実行した時、以下の環境でのみで発生する
オートメーションエラーで悩まされています。
発生環境
①：Windows7(64Bit)、Office2010(32Bit)
②：Windows10(64Bit)、Office2010(32Bit)

発生しているエラーは「実行時エラー -2147417848(80010108)
オートメーションエラーです。」というものです。

また、問題のExcelマクロですが拡張子が「.xls」であり、
Excel2003で作成したマクロであり、現在まで使用し続けている状態です。

エラーが発生する箇所は、以下のようなファイルオープン→シート取得→ファイルクローズを
繰り返し処理を行っている中での「ファイルオープン」箇所となります。
＜繰り返し処理＞
①対象のExcelファイルをオープンする
↓
②①でオープンしたExcelファイルからシートをコピーし、
自身のExcelマクロにコピーする。
↓
③①でオープンしたExcelファイルを閉じる。
↓
④①に戻り、次のExcelファイルをオープンする。

＜エラーが発生するソース箇所＞
Workbooks.Open PathFileName, passWord:=protectPass, readOnly:=False
※上記のイメージ図だと①でエラーが発生します。

ただ、エラーが発生するExcelファイルが決まっていません。
※例えば30番目のファイルでエラーが発生したかと思えば、
次は30番目のファイルは正常に読み込めるのに、35番目でエラーが発生する、など
基本は若い番目のファイルは正常にファイルオープンが行われています。

Workbooks.Openの前に「Application.Wait Now() + TimeValue("00:00:01")」
というような指定時間だけマクロを停止させる処理を追加したところ、
正常に動作しております。

一時しのぎで対応しているとはいえ、原因が特定できないため、
恒久対応が取れない状態です。

ご回答の程をよろしくお願いいたします。

Comments

[sage]

待ちを入れないと30～35ファイル程度でエラーになるのが、1ファイルごとに1秒の待ちを入れると何個のファイルでも問題なく動くのでしょうか?
(そのうちGCが動いて消してしまう)リソースが不足しているように見えます。
使っているobjectを不要になったら消す(nothingに設定)とうまくいったとのwebページは見つかりましたが、1秒待ったらOKになったこととの整合性は不明です。

[MeI0uQ9_zK]

返答が遅れてしまい、申し訳ありません。
早速のコメントありがとうございます。

＞待ちを入れないと30～35ファイル程度でエラーになるのが、1ファイルごとに1秒の待ちを入れると何個のファイルでも問題なく動くのでしょうか?

今のところ、最大70ファイル程度なのですが、問題なく動作しているようです。
ただ、今後は100ファイル程度まで増える可能性があるので、どうにかして原因を突き止め、
対応したいと考えています。

＞使っているobjectを不要になったら消す(nothingに設定)とうまくいったとのwebページは見つかりましたが、1秒待ったらOKになったこととの整合性は不明です。

確かに、プログラムを見直すと、多くのobjectを使用していますが、
明示的にnothingに設定はしていないので、全体を見直してみたいと思います。
進展がありましたら、改めてご報告させて頂きます。
ありがとうございました。

Answer 1

ども＾＾

繰返し処理で良く出ますよね。オートメーションエラー。
簡単にいうと、今回の件の場合、命令を受け付ける準備が出来てないのに、
矢継ぎ早に命令をすると、相手がパンクしますよね。
そういう状況と思われます。
Windowsのバージョンやエクセルのバージョンがどうのというよりも、
パソコンのパワーが非力なのが原因かなと思います。
（もちろんそれぞれのバージョンにより必要なパワーが違ってくるのですが。）
確実ではないですが、メモリーの増設等検討した方がいいかもです。

こういう場合、
命令を受け付ける準備が出来るまで待ってやるのが常套手段だと思います。

https://www.sejuku.net/blog/75643
↑こちらでは、パソコン側に負担を掛けないアイデアが紹介されてます。

ほかにも。
参考URL>>
http://www.excel.studio-kazu.jp/kw/20160608120832.html
http://officetanaka.net/excel/vba/tips/tips116.htm

コード的にどう書くかは、実際のコードが提示されてないのでなんとも、、、、

処理の流れ的には、

プログラム始め

フォルダ内のエクセルファイルに対し繰り返す
　　エラーがでたらエラー処理ルーチンにジャンプ
　　各ファイルを開く
　　エラー処理の無効化

　　もし、ファイルがひらけたなら
　　　　データのコピー
　　　　開いたファイルを閉じる

次へ

プログラムを抜ける

エラー処理ルーチン
　　もし、ファイルが存在するなら
　　　　0.1秒待つ
　　　　エラーが出た行に戻る

プログラム終わり

こんな構えのプログラムになると思います。
「VBA　エラー」でいろいろ検索して勉強してみるのもありかと思います。
逆にいままでエラー回避処理の必要性を認識せずに運用出来てたのも、
運が良かっただけとも言えますので、この際勉強してみてはいかがでしょうか？

Comments

[MeI0uQ9_zK]

返答が遅れてしまい、申し訳ありません。
コメントありがとうございます。

参考となるURLをご提示して頂き、ありがとうございます。
また、具体的なプログラムの流れもご提示頂き、ありがとうございました。
参考にさせて頂きます。

＞「VBA　エラー」でいろいろ検索して勉強してみるのもありかと思います。
＞逆にいままでエラー回避処理の必要性を認識せずに運用出来てたのも、
＞運が良かっただけとも言えますので、この際勉強してみてはいかがでしょうか？

確かにそうだと思います。
今回のようなエラー対応について勉強し、自身の経験につなげたいと思います。
ご確認とアドバイスありがとうございました。
進展がありましたら、ご報告させて頂きます。