ACCESSとExcelの連携操作について

質問させていただきます。

★行っていること
１．ACCESSで帳票作成
２．ACCESSフォームで出力（CopyFromRecordsetでクエリをExcelの任意のセルに出力）
※場合によりExcelファイルの数が違うためDo~loop処理を行っている
３．ACCESSからRunでExcelマクロを実行
４．任意の処理（製表、条件付き書式等）を実行
※最後の処理で上書き保存している

'''マクロ実行処理'''''''''''
        
        Set EE = CreateObject("Excel.Application")
            '本番はfalse
        EE.Visible = True
        EE.UserControl = False
        
        Set RS1 = DB.OpenRecordset("SELECT M_仕入先会社マスタ.仕入先コード,M_仕入先会社マスタ.会社名 FROM M_仕入先会社マスタ WHERE (((M_仕入先会社マスタ.[CHK])=-1));")

        Set wb = EE.Workbooks.Open("J:\シミュレート表\シミュレート表_2018\Macro.xlsm")
        Set ws = wb.Sheets("Sheet1")
        ws.Range("A1").CopyFromRecordset RS1
        
        EE.Run "シミュ_Macro"
        
        wb.Close
        EE.Quit
        
        Set wb = Nothing
        Set EE = Nothing
    
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("Q_CHK_Clear")
    DoCmd.SetWarnings True
    
    MsgBox "END"

End Sub

★困っていること
きちんとExcelマクロは実行され終了するのですが、処理が終わってACCESSに戻ってきたときにエラーが発生します。
『EE.Run "シミュ_Macro"』の部分です。
エラー内容はオートメーションエラー（実行時エラー440）です。
ネットなどで調べてみると、そのあとのCloseとQuitがないとExcelファイルが占有状態になると記載があったのですが。。。
ちなみにExcelのコードで最後に下記の処理を実施してACCESSに戻ろうとしています。

For Each wb In Workbooks
       If Not wb Is ThisWorkbook Then
         wb.Close SaveChanges:=True
       End If
Next wb    
ThisWorkbook.Close SaveChanges:=True

宜しくお願い致します。

行動規範の内容に同意します

回答3件

ベストアンサー

単純に、
"シミュ_Macro" 内で、
ThisWorkbook.Close SaveChanges:=True
で、自分自身を閉じているのに、
さらにAccessの方で、
wb.Close
ともう一度閉じようとしているのでエラーになっているように
思えます。

どちらかの Close を削除すればいいのでは。

ネットなどで調べてみると、そのあとのCloseとQuitがないとExcelファイルが占有状態になると記載があったのですが。。。

一回、Closeすれば十分でしょう。閉じてすでに存在してないものをさらに閉じようとすればエラーになるのは当然のことだと。

追記

サンプルを作成して実験してみました。

Excel の標準モジュール

vba
1Public Sub シミュ_Macro()
2    MsgBox "シミュ_Macro実行!"
3    ThisWorkbook.Close SaveChanges:=False
4End Sub

Accessの標準モジュール

Public Sub ZZZZZZ()
    Dim EE As Object, wb As Object

    Set EE = CreateObject("Excel.Application")
    '本番はfalse
    EE.Visible = True
    EE.UserControl = False

    Set wb = EE.Workbooks.Open("C:\TEST\MacroTest.xlsm")
    EE.Run "シミュ_Macro"

    wb.Close
    EE.Quit
    Set wb = Nothing
    Set EE = Nothing
    MsgBox "END"
End Sub

これを実行すると、EE.Run "シミュ_Macro"で、

text
1実行時エラー'440':
2オートメーションエラーです。

とエラーがでます。
オープンしたwbが シミュ_Macro 内でCloseされて参照できなくなったからだと思われます。

シミュ_Macro内のThisWorkbook.Close SaveChanges:=Falseをコメントアウトして実行したら、
エラーなく最後まで実行され、"END" とメッセージが表示されました。

投稿2018/06/29 10:00

編集2018/06/30 16:14

hatena19

総合スコア34367

napoleon

2018/07/02 02:09 編集

コメントありがとうございます。ご指摘のあったThisWorkbook.Close SaveChanges:=Falseを外したら「END」まで流れました。しかし、ExcelBook『Macro』が起動されたままで保存しますか？というメッセージが表示されます。『Macro』も保存したいのですがこの場合、ACCESS側から保存を指示することは出来るのでしょうか？追記：wb.CloseとEE.Quitをコメントアウトすると表示されなくなりました。この解釈で間違ってないないのでしょうか。。。皆さんからいただいたコメントでは2回Closeをしているのが原因と書かれていたので。。。Excelをチェックすると製表され保存された状態なのですが少々不安で。。。

hatena19

2018/07/02 03:25

編集して保存したいのですね。ならば、Access側のコードで、 wb.Close SaveChanges:=True とすればどうでしょう。あるいは、Excel側のマクロの最後で、 ThisWorkbook.Save というように上書き保存すればどうてしょう。

napoleon

2018/07/02 04:19

ありがとうございます。上書き保存することが出来ました。あと一つご教示いただきたいのですが、製表したExcelを開くとなぜかMacroのファイルも立ち上がってしまいます。ACCESS側で解放しているはずなのですが、うまくできてないということなのでしょうか？処理が終了した時点でタスクマネジャーで確認してもExcelは立ち上がっていないのですが。。。

hatena19

2018/07/02 05:37

「製表したExcel」とは、「シミュ_Macro」で新規にワークブックを作成しているということでしょうか。新規に質問しなおして、そこで、症状が再現できるようなサンプルのコード（Excel側とAccess側の両方の）を提示し、状況を詳してく説明したほうがいいかと思います。

napoleon

2018/07/02 05:49

ありがとうございます。新たに質問したいと思います。色々ご教示いただきありがとうございました。

行動規範の内容に同意します

まず、デバッグするならエクセルを表示させて、
どういう挙動か見て確認するのが早いでしょうね。

で、SaveChanges:=True は SaveChanges:=False がいいんじゃない？
たぶん、なんかダイアログが出てるんじゃぁないかなぁ？？

あと、Excel側で自分自身を閉じる処理があって、
Access側でも該当ブックを閉じる処理があるから、
そこでもデバッグが起きちゃうはず。

個人的には、そういう処理はExcel側にもたせず、
Access側で制御した方が分かりやすいと思うな。

共通関数化しておいて、流用する方がこういう時にメンテしやすいし、
Excelの処理を確認しにいかなくていいし、
楽になると思うよ。

細かいところかもだけど、
レコードセットの結果が０件だとどうなっちゃうのかな？
シートにヘッダーだけ張り付くのだろうか？
今のところ０件はあり得ないとしても、
ちゃんとしといた方が無難だろうね。

投稿2018/06/29 07:07

ExcelVBAer

総合スコア1175

napoleon

2018/06/29 07:33

コメントありがとうございます。ご指摘いただいたSaveChanges:=True は SaveChanges:=False に変更しました。また、一度エクセルを表示させて挙動を確認してみました。試しに２つのファイルをセットし、２回処理を走らせ見たところ・該当のExcelファイルに対し処理が走った・２回処理が終わってLoopから抜けたところで、保存処理が行われた・Excelが全て終了し、ACCESSの『EE.Run "シミュ_Macro"』に戻ったそして、今度は実行時エラー1004がでました。このブックでマクロが使用できないかすべてのマクロが無効になっているかとのメッセージが出ました。確かにExcelは閉じてしまっているのでこのエラーが出るのかなとは思いますが。。。回避策などはあるのでしょうか？・

行動規範の内容に同意します

一旦全て閉じてから、CreateObject("Excel.Application")から始まる処理に変更してみてはどうでしょうか。

但し、マクロの実行については、ファイルのパスから指定する必要があると思います。

VBA
1'''マクロ実行処理'''''''''''
2        Set EE = CreateObject("Excel.Application")
3'~　略 ~
4        Set ws = wb.Sheets("Sheet1")
5        ws.Range("A1").CopyFromRecordset RS1
6        wb.Close
7        EE.Quit
8        Set wb = Nothing
9        Set EE = Nothing
10
11        Set EE = CreateObject("Excel.Application")
12        EE.Run エクセルファイル名 + "!シミュ_Macro"
13        EE.Quit
14        Set EE = Nothing
15
16    DoCmd.SetWarnings False
17    DoCmd.OpenQuery ("Q_CHK_Clear")
18    DoCmd.SetWarnings True
19    MsgBox "END"
20End Sub