エクセルVBAでシートの複製時にオートメーションエラーが発生する

Question

エクセルVBAでどうしても解決できないオートメーションエラーがあるので相談させてください。

見積書を請求書に変換するというマクロを作っております
具体的にはこのような処理の流れになっています。

①同一フォルダ内の請求書.xlsmブックを開く
②請求書.xlsmブックの先頭に見積シートのコピーを作成
③タイトルやシート名を変更

この時、②の処理で「オートメーション エラーです。起動されたオブジェクトはクライアントから切断されました」というメッセージが表示されます。

デバッグボタンを押すと、こちらの部分がハイライトされます。
ThisWorkbook.Worksheets(estimateSheetName).copy Before:=Workbooks("請求書.xlsm").Worksheets(1)

また、エラーは毎回発生するのではなく発生したり、しなかったりで再現性がありません。

特段重い処理では無いように思いますし、コピーするシートも一般的な見積書で、大量のデータやシェイプを使用したものではないと思います。
なぜこのような事が起きるか分かる方いらっしゃいますでしょうか？

発生している問題・エラーメッセージ

オートメーション エラーです。起動されたオブジェクトはクライアントから切断されました

該当のソースコード

Sub duplicateEstimateToInvoice()
'見積書シート名を取得'
Dim estimateSheetName As String
estimateSheetName = ThisWorkbook.ActiveSheet.Name

' 請求書ファイルを開く'
Dim invoiceBookPath As String
invoiceBookPath = ThisWorkbook.path & "\請求書.xlsm"
Workbooks.Open invoiceBookPath

'請求書ファイルの先頭に複製を作成'
ThisWorkbook.Worksheets(estimateSheetName).copy Before:=Workbooks("請求書.xlsm").Worksheets(1)

'以下タイトルやシート名を変更する処理'

End Sub

試したこと

・onedriveの共有対象から外す
・請求書.xlsmを開いた際にworkbook_openイベントで自動で実行するマクロを登録していましたが、それを削除
・キャッシュを削除
・ブックの修復
・Application.ScreenUpdating = Falseをマクロの最初に記入
・ブックを新規作成して作り直す（ブックが破損している可能性があるので）

いずれも上手くいきませんでした・・・

補足情報（FW/ツールのバージョンなど）

windows10
Excel for microsoft 365
CPU intel10世代i5
メモリ16GB

Comments

[meg_]

コードは「コードの挿入」で記入してください。

[meg_]

invoiceFilePathは定義されいませんが？

[bktclvy]

申し訳ありませんでした。
質問の書式を整えました。
また、invoiceFilePathとなっていた部分はinvoiceBookPathの間違えですので、そちらも修正いたしました。

[meg_]

> また、invoiceFilePathとなっていた部分はinvoiceBookPathの間違えですので、そちらも修正いたしました。
直っていないです。

ちなみに、上記コードを試したところエラーは出ませんでした。
質問者さんの環境でエラーが出るとしたら環境に問題があるのかもしれません。Excel以外のアプリケーションを多く起動していたりはしませんか？

Answer 1

Sub duplicateEstimateToInvoice()
    Dim wbk As Workbook
    
    ' 請求書ファイルを開く'
    Set wbk = Workbooks.Open(ThisWorkbook.Path & "\請求書.xlsm")

    '請求書ファイルの先頭に複製を作成'
    ActiveSheet.Copy Before:=wbk.Worksheets(1)

    '以下タイトルやシート名を変更する処理'
    With wbk.Worksheets(1)

    End With
End Sub

これだといかがでしょうか？

Comments

[bktclvy]

すごいです！
エラー発生しなくなりました！
なぜこちらの書き方だとここまで変化が出るのか、理由を教えていただく事はできないでしょうか？

[mattuwan]

ん～言語化する知識を持ってないですが、
Workbooks.Open invoiceBookPathと書くと、
命令だけだして、開いていようが開いてなかろうが、次の行をすぐ実行してしまう？

Set wbk = Workbooks.Open(ThisWorkbook.Path & "\請求書.xlsm")
とOpenメソッドの返り値を使うようにすると、
参照が変数に代入できるようになるまで(ファイルが開くまで）待つのでは？

[mattuwan]

Workbooks.Open "請求書.xlsm"
ThisWorkbook.Worksheets(estimateSheetName).copy Before:=Workbooks("請求書.xlsm").Worksheets(1)

えっと、追記。理屈はさておき、
こうやって書いたとき、
開いたブックとWorkbooks("請求書.xlsm")が同じか実行するまでわからないけども
（開ききってない可能性もあるし）、

Set wbk = Workbooks.Open(ThisWorkbook.Path & "\請求書.xlsm")
って書いたら、変数「wbk」の中身は開く命令で開いたブックってことが担保されるので、
エラーにならない（エクセル君が今開いたブックと確実に理解してくれる）かなと提案してみました。

＞なぜこちらの書き方だとここまで変化が出るのか
そんな変化があります？
エラーが出るか出ないかの違いでは？
相手が（エクセル君が）確実に作業できる作業標準書（プロシージャ）を作成することに、
注力してくださいませ。

[mattuwan]

estimateSheetName = ThisWorkbook.ActiveSheet.Name
ThisWorkbook.Worksheets(estimateSheetName).copy

あと、同じアクティブシートを示すのにこういうのは無駄だからやめましょう。

[bktclvy]

ありがとうございます！
なるほど、ブックが開くのを待たずに次の処理に行ってしまっていたのですね。
このような書き方であればブックが確実に開いた事が担保できるという事とても参考になりました。

ここ1週間ずっとモヤモヤしていたのでとても助かりました！