質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

Q&A

解決済

3回答

10949閲覧

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

napoleon

総合スコア18

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

0グッド

0クリップ

投稿2018/06/29 06:15

編集2018/06/29 06:50

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

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

'''マクロ実行処理''''''''''' 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

宜しくお願い致します。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

単純に、
"シミュ_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

総合スコア33610

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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

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

0

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

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

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

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

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

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

投稿2018/06/29 07:07

ExcelVBAer

総合スコア1175

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

napoleon

2018/06/29 07:33

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

0

一旦全て閉じてから、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

投稿2018/06/29 06:42

編集2018/06/29 07:02
sazi

総合スコア25085

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

napoleon

2018/06/29 06:48

コメントありがとうございます。一旦すべて閉じるというのはExcelのお話でしょうか? マクロの実行はうまくできているので、その後ACCESSで解放する必要があると持っていたのですが、間違いなのでしょうか??
sazi

2018/06/29 07:02

追記しました。
napoleon

2018/06/29 07:21

コメントありがとうございます。教えていただいたコードを試してみたのですが、うまく行きませんでした。 なぜか再度立ち上げたExcelには毎回マクロの有効化を聞かれ、マクロを実行すると2回実行するはずの処理が1回しか走りませんでした。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問