VBA 複数のBOOKの連続操作

質問

いつもお世話になっております。
複数のブックの更新操作についてわからないことがあります。

複数のブックには項目別のWEBクエリが設定されており、
更新の際にはその都度ブックを開いた上更新しています。

BOOKは４つあり、
BOOK１のコマンドボタンからBOOK2～BOOK４までの更新を実行したいと思っています。

下記のように作ってみたのですが、
BOOK２を更新して閉じた後、BOOK3への処理はスルーされています。
エラーが出るわけではないのですが・・・

素人のため、説明も要所を得ていませんが、
どうかお力を貸していただけないでしょうか？

下記　BOOK1のマクロ
Private Sub CommandButton9_Click()
'ブック２を開いてWEBクエリ更新
Workbooks.Open Filename:="D:\TestBook\test.xlsm"
WBN = ActiveWorkbook.Name
Application.Run "'" & WBN & "'!Module3.更新して閉じる"
'ブック３を開いてWEBクエリ更新
Workbooks.Open Filename:="D:\TestBook\test2.xlsm"
WBN = ActiveWorkbook.Name
Application.Run "'" & WBN & "'!Module3.更新して閉じる"

End Sub

BOOK2～BOOK４
Module3

Sub a1()
'
エラーメッセージを表示する（On Errorステートメント）
On Error GoTo Err

'--------------------------------------------------------
'webクエリ更新
Range("B6").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

’中略

'エラーならブックを上書き保存して閉じます
Err:

Sheets("sheet1").Select
Range("C2").Select
Selection.ClearContents
Range("P3").Select

If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
End If
ActiveWorkbook.Close

End Sub

宜しくお願いいたします。

回答No.4

jcctaira · ベストアンサー

tokyo2199さん
おつかれさまです。

>上記マクロを上からブレークポイントF8キーで順に実行していったのですが、
>●ポイントからF8キーで次処理に移行しようとすると、→始点に戻ってしまいます。
>BOOK２のマクロに問題があるのかも知れませんが、

私も同じ内容(EXCEL2010）で作成しましたが、正しく動作します。
　（●ポイントから→始点には戻らない）

BOOK２の内容の影響かも知れませんね。
tokyo2199さんのEXCELデータを頂ければ、確認できますが…。
このサイトでは難しそうですね。

申し訳ありませんが、頑張って自分で調査・確認してください。

お礼

色々とありがとうございました。
大変勉強になりました。

回答No.3

jcctaira

> BOOK2の処理後続けてBOOK３へ同様の処理を行いたいのですが、その処理へ移行しないみたいです。
BOOK3の状態が分からないので、何とも言えませんが…

「確認です」
1.Workbooks.Open Filename:="D:\TestBook\test２.xlsm"　は正常に読み込めたのですよね。
2.test２.xlsm のモジュールに「Module3.更新して閉じる」はあるのですよね。

エラーの時は試行錯誤が必要です。

では処理順序を変更し、
　BOOK3を最初に処理し、BOOK2をその次にするとどうなりますか？
　BOOK2が正常に動作するのなら「確認です」は問題がないですね。

次にステップ実行で、BOOK2の処理後ブレークし、そのあと１ステップずつ動かして
　確認し、正常に動作しているかを確認してください。

※テストを直接見られないので指示して申し訳ないですが…

補足

遅くなりました。

>BOOK3を最初に処理し、BOOK2をその次にするとどうなりますか？
順序を変えてみましたが、状況は変わらないようです。

>BOOK2が正常に動作するのなら「確認です」は問題がないですね。
はい、こちらは問題ありませんでした。

>次にステップ実行で、BOOK2の処理後ブレークし、そのあと１ステップずつ動かして確認し、正常に動作しているかを確認してください。

→　Private Sub CommandButton9_Click()

　　'ブック２を開いてWEBクエリ更新
　　Workbooks.Open Filename:="D:\TestBook\test.xlsm"
　　WBN = ActiveWorkbook.Name
　　Application.Run "'" & WBN & "'!Module3.更新して閉じる"
　　Application.DisplayAlerts = False
● Workbooks(WBN).Close
　　'ブック３を開いてWEBクエリ更新
　　Workbooks.Open Filename:="D:\TestBook\test2.xlsm"
　　WBN = ActiveWorkbook.Name
　　Application.Run "'" & WBN & "'!Module3.更新して閉じる"
　　Application.DisplayAlerts = False
　　Workbooks(WBN).Close

上記マクロを上からブレークポイントF8キーで順に実行していったのですが、●ポイントからF8キーで次処理に移行しようとすると、→始点に戻ってしまいます。
BOOK２のマクロに問題があるのかも知れませんが、
エラーが出ないのが不思議です。

BOOK３はBOOK２の複製で、中身のマクロは同じもです。
内容は違うものの、同じ処理をするようになっています。

＊　
--------------------------------------------------
　　Private Sub CommandButton9_Click()

　　'ブック２を開いてWEBクエリ更新
　　Workbooks.Open Filename:="D:\TestBook\test.xlsm"
　　WBN = ActiveWorkbook.Name

　　Application.DisplayAlerts = False
　　Workbooks(WBN).Close
　　'ブック３を開いてWEBクエリ更新
　　Workbooks.Open Filename:="D:\TestBook\test2.xlsm"
　　WBN = ActiveWorkbook.Name

　　Application.DisplayAlerts = False
　　Workbooks(WBN).Close

End Sub

上記のように、
　「Application.Run "'" & WBN & "'!Module3.更新して閉じる"」
を省いた状態で実行すれば、BOOK1：オープン→クローズ→BOOK2→オープン→クローズ　と最後まで実行できました。

回答No.2

jcctaira

> Application.Run "'" & WBN & "'!Module3.更新して閉じる"
> を実行すると、次のブックへの処理が実行されないようです。

　すみません、言っている意味が分かりません。
　tokyo2199さんのプログラムは全く修正していないと思います。

最初の質問で
> BOOK２を更新して閉じた後、BOOK3への処理はスルーされています。

　BOOK2の「Application.Run "'" & WBN & "'!Module3.更新して閉じる"」
　は正常に動作すると理解したのですが…？

> Module3
> Sub a1()
とのことでしょうか？

それなら
　Application.Run "'" & WBN & "'!Module3.a1"
だと思います。

理解ができず、申し訳ありませんが再度確認をしてください。

補足

こちらの説明の仕方が悪いです。
言葉足らずの上、要所を伝えられず申しわけありません。
>BOOK2の「Application.Run "'" & WBN & "'!Module3.更新して閉じる"」は正常に動作すると理解したのですが…？

はい、問題なく処理は行われています。しかし、BOOK2の処理後続けてBOOK３へ同様の処理を行いたいのですが、その処理へ移行しないみたいです。

書き忘れていたのですが、
「Book1」　
　Workbooks.Open Filename:="D:\TestBook\test.xlsm"
　WBN = ActiveWorkbook.Name
　Application.Run "'" & WBN & "'!Module3.更新して閉じる"
　Workbooks(WBN).Close
'BOOK3への処理を続けて行う
　Workbooks.Open Filename:="D:\TestBook\test２.xlsm"
　WBN = ActiveWorkbook.Name
　Application.Run "'" & WBN & "'!Module3.更新して閉じる"
　Workbooks(WBN).Close

上記のようにBOOK１へBOOK2のあとにBOOK３にも同様の処理をするように記述していました。
教えていただく立場にありながら、適切に質問内容を伝えられず、申しわけありません。

こんな素人丸出しの私に付き合っていただき心より感謝しています。理解するのにも大変かと思いますが、理解できる範囲で結構ですので、宜しくお願いします。

回答No.1

jcctaira

tokyo2199さん　こんにちは。

「更新して閉じる」とSub「a1」が同じとして回答します。
原因はBOOK2で自分のブックを閉じてしまうので、End Subまでを実行できず、BOOK1に制御が
戻らない（マクロが終了）ことです。

対策としては「BOOK2～4」はブックをCloseせず「Book1」でCloseするようにすれば良いです。
例）
「Book1」
　Workbooks.Open Filename:="D:\TestBook\test.xlsm"
　WBN = ActiveWorkbook.Name
　Application.Run "'" & WBN & "'!Module3.更新して閉じる"
　Workbooks(WBN).Close

「Book2」
　If ThisWorkbook.Saved = False Then　ThisWorkbook.Save
　End Sub

確認してみてください。

補足

ありがとうございます。
確認してみましたところ、
Application.Run "'" & WBN & "'!Module3.更新して閉じる"
を実行すると、次のブックへの処理が実行されないようです。
この記述を省くと、BOOK１からブック２を開き閉じる。→ブック３を開き閉じる。と処理を続行できました。

変更点はご指摘のように
「Book1」
　Workbooks.Open Filename:="D:\TestBook\test.xlsm"
　WBN = ActiveWorkbook.Name
　Application.DisplayAlerts = False
　Application.Run "'" & WBN & "'!Module3.更新して閉じる"
　Workbooks(WBN).Close

「Book2」
　If ThisWorkbook.Saved = False Then　ThisWorkbook.Save
　End Sub
のようにしています。
お手数おかけしますが、宜しくお願いいたします。