ACCESSからEXCELのデータを取得する際に、EXCELプログラムを終了させることについて

Question

ACCESSファイルからEXCELファイル（ACCESSファイルと同じフォルダ内にある、[aaa.xlsx]）
のデータを取得する際に次のようなコードを書きました。

---------------------------------------
Sub Test1()　　　　'(エクセルの参照設定はしていません)

Dim obj As Object

Set obj = CreateObject("Excel.Application")
obj.Application.Visible = True
Dim str1 As String

Dim i As Integer
Dim j As Integer

str1 = CurrentProject.Path & "\aaa.xlsx"

With obj.Workbooks.Open(str1)

	For i = 1 To 3

   		For j = 1 To 3
    	        Debug.Print .Worksheets("Sheet1").Cells(i, j).Value
    	   Next j

	Next i

End With

obj.Quit
Set obj = Nothing

End Sub

----------------------------------------------
次に、　エクセルの参照設定をしたうえで、
次のようなコードを書きました。

Sub Test2()

Dim str1 As String

Dim i As Integer
Dim j As Integer

str1 = CurrentProject.Path & "\aaa.xlsx"


With Workbooks.Open(str1)

	For i = 1 To 3

    	For j = 1 To 3
    	Debug.Print .Worksheets("Sheet1").Cells(i, j).Value
   		Next j

	Next i

End With

End Sub

------------------------------------------------

Test2ではEXCELアプリケーションのオブジェクト（CreateObject）は作っていませんが、
プログラムは作動します。
ただ、バックグラウンドで生成された？EXCELアプリケーションが残ったままに
なってしまいました。

質問1

Test2で生成されたEXCELアプリケーションをきちんと消去する方法はありますか？

End with　の次に「Excel.Application.Quit」を入れて試しましたが、消えませんでした。

質問2

(質問1に関連しますが)Test2のようなコードの書き方はよくないのでしょうか？
CreateObjectしなくても済むなら。。。。と思ったのですが。

最初に思ったのは、CreateObjectしないから、オブジェクトを終わらせる処理
(Set *** = NothingとかExcel.Application.Quitとか)を入れなくて済む
のではないかと考えましたが、CreateObjectしてもしなくても
EXCELのプロセスが発生するのならば、CreateObjectをして、そのオブジェクトを
Quitするべき、なのでしょうか？(ただ、Test2でも動くんですよね。。)

何かとんちんかんなことを質問しているのかもしれませんが、よろしくお願いします。

Answer 1

質問1

提示のコードに、エクセルApplcationの終了を追加するなら、下記のコードになりますね。
追記: 試してみたら、Set App = .Applcationでエラーになりました。
Withを使わず変数宣言して Set wb = Workbooks.Open(str1) で開いて wb.Application.Quit で終了させたらエラーはなくなりましたが、タスクマネージャで確認したら、エクセルApplcation は残ってました。下記のTest3以降のコードの場合は、エクセルApplcationはきれいに消えました。

Sub Test2()

Dim str1 As String

Dim i As Integer
Dim j As Integer

str1 = CurrentProject.Path & "\aaa.xlsx"


With Workbooks.Open(str1)

    For i = 1 To 3

        For j = 1 To 3
        Debug.Print .Worksheets("Sheet1").Cells(i, j).Value
           Next j
    Next i
    Dim App As Excel.Application
    Set App = .Applcation  'WorkBookの親のエクセルApplcationを取得
    
    .Close   'WorkBookを閉じる
    App.Quit 'エクセルApplcationを終了

End With

End Sub

下記の点を理解してください。

• 参照設定してあると、Workbooks.Open すると裏でエクセルApplcationが自動生成される。
• Workbooks.Open はWorkbookオブジェクトを返す。
• Workbookの親のエクセルApplcationは、Workbookオブジェクトの Applicationプロパティで取得できる。

ただ、参照設定をしてあるなら、下記のコードが素直で読みやすいコードだと思います。
（ちなみに、私は、変数宣言は使う前にする派です。）

Sub Test3()

    Dim str1 As String
    str1 = CurrentProject.Path & "\aaa.xlsx"

    Dim app As Excel.Application
    Set app = New Excel.Application 'エクセルApplicationの生成

    With app.Workbooks.Open(str1)
        Dim i As Integer, j As Integer
        For i = 1 To 3
            For j = 1 To 3
                Debug.Print .Worksheets("Sheet1").Cells(i, j).Value
            Next j
        Next i
        .Close   'WorkBookを閉じる
    End With
    app.Quit 'エクセルApplcationを終了

End Sub

参照設定をしてある場合、NewステートメントでエクセルApplication を生成します。
CreateObjectでもできますが、普通は New です。

変数宣言をせずに、With を入れ子にする方法もあります。

Sub Test4()

    Dim str1 As String
    str1 = CurrentProject.Path & "\aaa.xlsx"

    With New Excel.Application
        With .Workbooks.Open(str1)

             '中略

            .Close   'WorkBookを閉じる
        End With
        .Quit 'エクセルApplcationを終了
    End With
End Sub

この辺はプログラマーの好みですが、私は前者の変数宣言する方法を採用する場合が多いかな。
入れ子にすると、長いコードだと、どのオプジェクトなのか判別しづらい場合があるので。

質問2

上のコードを見ればわかると思いますが、
Excel.Application を生成したら、自動で破棄してくれませんので、
Quit は必須ですね。
裏で自動で生成されたとしても。

Comments

[sazi]

検証はどのような環境で行われました？
Windows10(64bit), access2016,office2016
上記の環境で質問の内容コードを実行して、タスクマネージャからは消えました。

[hatena19]

「質問の内容コード」とは、どのコードことですか。
質問の Test2() は、質問者自身が、「EXCELアプリケーションが残ったまま」といってます。
当方の環境 (Windows10(64bit), access2019, office2019) でも、タスクマネージャに残るのを確認してます。

[sazi]

「質問の内容コード」とは質問に記述されているコードです。
質問者さんはOffice365と言われてましたので、気になりました。
現象は同じでも、解決策が同じとは限らないので。

[hatena19]

質問には、Test1() と Test2() がありますが、Test2() の方で、タスクマネージャに残りませんか。
また、タスクマネージャで確認する場合、「バッググラウンド プロセス」の方にも残っていないのは確認してますか。

[sazi]

バックグラウンド確認していませんでした・・・
明示的でないExcel.Applicationは、破棄する術がない
という事ですね。

[u_zu]

どうもありがとうございます。質問とは少しそれますが、Newステートメントなども勉強になりました。
Sub Test3 の書き方、いいですね。

[TanakaHiroaki]

横からすみません。
VBAオブジェクト変数の宣言時にNewステートメントを使用し、１行ですませる方法もよく見かけますが、後でインスタンスを破棄したい場合に問題となります。以下にその理由が書いてあります。
https://thom.hateblo.jp/entry/2016/09/27/221527

hatena19さんは、このような内容まで熟知されたうえでTest3のコードを書かれていると思います。

Answer 2

質問1

試していませんが、次のようにしては如何でしょうか。

With Workbooks.Open(str1)
    '略

    .Application.DisplayAlert = False
    .Application.Quit
End With

質問2

素直にCreateObjectを使う方法のほうが間違いないと思うのですが、何れにせよQuitは必須す。

Nothingの代入は省略してもあまり問題にならない気がしますが、気持ち悪い場合はWithで固めてしまうという手もあると思います。

Sub ExcelTest()
    
    'With New Excel.Application '参照設定するならコッチのほうが便利かも
    With CreateObject("Excel.Application")
        
        '処理

        With .Workbooks.Open(str1, False, True)

            '処理
        
            .Close False
        End With
        
        .DisplayAlerts = False
        .Quit
    End With
    
End Sub

P.S.

End with　の次に「Excel.Application.Quit」を入れて試しましたが、消えませんでした。

もしかしたら保存確認メッセージが邪魔してたりして？

Comments

[u_zu]

回答ありがとうございます。どちらの場合もEXCEL消えませんね。。。

[KotorinChunChun]

それはおかしいですね。

後者の場合は適切に生成して破棄しているので、消えないということは考えにくいです。

２はちゃんとテストしていますが、正常にEXCELのプロセスは終了しています。

ただ類似コードをhatenaさんが追記されたので私は降ります。

[u_zu]

＞２はちゃんとテストしていますが、正常にEXCELのプロセスは終了しています。
私がテストした限り、Quitの後で、エクセルのプロセスは消えていません（プロシージャを抜ければ消えます）

[TanakaHiroaki]

End With の後でプロセスが終了するのではないでしょうか？

[u_zu]

いえ、End Withのあとでは消えません。End Subまでいかないと消えません（失礼ですが、試されましたか？）

[KotorinChunChun]

確かに、Quitの時点ではまだWithが捕まえているので消えないでしょうね。
でもEnd Withの後に消えると思います。

```
End With

Stop

End Sub
```
のようにしたり、End Subにブレークポイントを設定して確認した所、こちらの環境では正常にExcelのプロセスは消滅を確認しました。

ただし、Excelのプロセスが消えるまでには１，２秒のラグがあります。それで誤解した可能性もありますし、もう一度試されては如何でしょうか。

[TanakaHiroaki]

私はACCESSを持っていないのでEXCELからWORDを起動して処理するケースで試してみました。
.Quitの後で、WORDのプロセスが消えました。（EXCEL2016／WORD2016）

[u_zu]

こちらの環境では　1分以上経過しても消えませんね。。。タスクマネージャーのバックラウンドプロセスにエクセルが残ったままです。

[TanakaHiroaki]

環境の違いによるものかもしれません。
必要であれば、プロセスハンドルを捕まえてEXCELを終了させるコードを書くとよいでしょう。

[KotorinChunChun]

食い違いの原因がわかったかもしれません。

With CreateObject("Excel.Application") の場合は、End Withを抜けてもプロセスが落ちませんでした。

With New Excel.Applicationの場合は、End Withを抜けた後にプロセスが落ちました。

すでに解決済み案件ではございますが、参考までに結果を教えていただけると嬉しいです。

この例を提案しておいてアレですが、私もhatenaさんのようにExcel.Applicationは変数に入れて実行するほうが良いと思います。

ただ、AccessからExcelを呼ぶ時は、参照設定が壊れやすいので、リリース時にはObject型変数に戻して、CreateObject("Excel.Application")に変えるようにしています。

[u_zu]

With new Excel Application の場合は　確かに落ちます、少し時間がかかりますが（3秒くらい）。色々難しいですね。

[KotorinChunChun]

報告ありがとうございます。参考になりました。

Answer 3

外してるかもですが…
Workbooks.Open でなんか返ってきてないですかね?
http://doctor.ataglance.jp/mini-macro11/

それに対して close なりなんなりするのはどうでしょうか。

Comments

[u_zu]

回答ありがとうございます。Workbooks.Open でWorkbookオブジェクトが返るのですが、
Sub Test2() では、その返り値となるWorkbookオブジェクトを生成していないので
(Test1では [obj]を生成しているのでQuitできる)、Closeできる対象が無いんですよ。
Closeできる対象を見つける方法がありますか？

Answer 4

DoCmd.TransferSpreadsheetでリンクするとかインポートする方がクエリーとか使えます。

Comments

[u_zu]

回答ありがとうございます。それもあると思うのですが、データを細かく編集したりすることもあるので。。。　私のイメージでは、accessでいう、Recordsetのようなものにエクセルのデータを格納して、いろいろとやれるように（運用上の柔軟性を持たせたい）したいのですが。

[sazi]

リンクテーブルをRecordsetで開いてあげれば良いのでは？
リンクテーブルならエクセルが残る問題は解決しますよ

[u_zu]

なるほど。。。ありがとうございます。参考にします。　それとは別に、純粋に質問の方はやはりACCESSからEXCELという、あるアプリから異なるアプリを操作している以上、別のアプリ（この場合はEXCEL）をCreateObjectで参照する変数を作るべきなのでしょうか？

[sazi]

close入れても終了しないとの事なので、何か見落としがあるかもしれませんね。
ちょっと古いですけど、以下参考に。
Application.Quitの誤解(http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_800_060.html　)

因みに、accessとエクセルのバージョンは何でしょう？

[u_zu]

バージョンはOffice365 soloです(Office2016相当？バージョン1905)。　書いていただいたＵＲＬは開くことができないです。。。(HTTP404未検出)

[sazi]

url修正しました

それから、
With obj.Workbooks.Open(str1)
となっているので
set wkbk = obj.Workbooks.Open(str1)
のように変数に受けて、それをcloseしてみて下さい。

[u_zu]

ありがとうございます。頂いたURLの記事では、EXCELプログラムの中で、そのEXCELを
終了する、という内容だと思います（EXCELのモジュール内で、「Application.Quit」と
記述するということは、「Application」はEXCELを指しますよね？）。

私の質問の方では、ACCESSからEXCELファイルを操作するので、「Application.Quit」の
「Application」の対象はACCESSだと思います、
TanakaHiroakiさんの回答に返答した、「ActiveWorkBook.Close SaveChanges:=False　」と
いう部分のところでも、ActiveWorkbook自体が非表示の状態なのでうまくいかないのではないか？
と想像しております。。。

Excel.Applicationを指定する方法があるのか、分かりません。。。

[u_zu]

それから、
＞With obj.Workbooks.Open(str1)
＞となっているので
＞set wkbk = obj.Workbooks.Open(str1)
＞のように変数に受けて、それをcloseしてみて下さい。

なるほどです。

[u_zu]

色々、ありがとうございます。

[sazi]

外部データを扱うのだから、accessで扱いやすい形式にしてから処理するという回答の趣旨さえ汲み取って貰えれば。

Answer 5

質問1 ですが、End with　の次に以下を追加してはどうでしょうか。

obj.ActiveWorkBook.Close SaveChanges:=False

Comments

[u_zu]

どうもありがとうございます。　頂いたコードを入れると、「obj」は定義されていないので、エラーになってしまいます。
ActiveWorkBook.Close SaveChanges:=False　だけを入れるとエラーにならず、プログラムは動きますが、EXCELプロセスは消えません。