vba Excel.Application Object .Quit 使 EXCEL.EXE 运行

Excel.Application Object .Quit leaves EXCEL.EXE running

提问by Cinetyk

I'm working within MS Access 2013 on Windows 10 and I'm simply trying to write a Sub that opens an Excel file on disk, changes the column formatting and some columns, saves and quits. The Sub runs and does as intended, but the problem is that even after the .Quit command, "EXCEL.EXE" keeps running and subsequent calls to that Sub will result in run-time errors. If I close Access after running the sub, "EXCEL.EXE" disappears on the Task Manager, but not if I do "Compact and Repair" the database.

我在 Windows 10 上的 MS Access 2013 中工作，我只是想编写一个 Sub 来打开磁盘上的 Excel 文件，更改列格式和一些列，保存并退出。Sub 运行并按预期执行，但问题是即使在 .Quit 命令之后，“EXCEL.EXE”也会继续运行，随后对该 Sub 的调用将导致运行时错误。如果我在运行子程序后关闭 Access，任务管理器上的“EXCEL.EXE”会消失，但如果我执行“压缩和修复”数据库，则不会。

I've done one other Sub that simply opens an Excel file on disk and alters te width of all columns to "auto-width", then closes, which works fine and doesn't leave "EXCEL.EXE" running.

我已经完成了另一个 Sub ，它只是在磁盘上打开一个 Excel 文件并将所有列的宽度更改为“自动宽度”，然后关闭，这工作正常并且不会让“EXCEL.EXE”运行。

What am I doing wrong? Is it possible in VBA to make sure that "EXCEL.EXE" correctly quits?

我究竟做错了什么？是否可以在 VBA 中确保“EXCEL.EXE”正确退出？

I tried a lot of different line order on Dims and Sets to Nothing, Workbook Closes, etc etc. Also, I searched here and on other sites on how to solve this, but after 2h the only suggestions I've seen for VBA is using something like ThisWorkbook.Saved = True, but I tried that before and after .Quit to no effect. Other than that, I only find solutions for VB.NET or other environments that I'm not using and now next to nothing of.

我在 Dims 和 Sets to Nothing、Workbook Closes 等上尝试了很多不同的行顺序。 另外，我在这里和其他网站上搜索了如何解决这个问题，但 2 小时后，我看到的 VBA 的唯一建议是使用ThisWorkbook.Saved = True 之类的东西，但我在 .Quit 之前和之后都尝试过，但没有效果。除此之外，我只找到 VB.NET 或其他我不使用的环境的解决方案，现在几乎没有。

Thanks for reading this.

感谢您阅读本文。

Code follows:

代码如下：

Sub changeXLcolumnFormatting()

Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = False
XL.DisplayAlerts = False
XL.Workbooks.Open "C:\Users0344\Desktop\rawDataTest.XLSX"
Dim sht As Worksheet

With XL
Set sht = ActiveWorkbook.Worksheets(1)

Dim rng As Range
Dim i As Integer, j As Integer

field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
sht.Select
end_of_table = sht.UsedRange.Columns.Count

For j = 0 To UBound(field_names)
    For i = 1 To end_of_table
        Set rng = sht.Cells(1, i)
        If InStr(rng.Text, field_names(j)) > 0 Then
            sht.Columns(i).NumberFormat = "yyyy-mm-dd  HH:MM:ss"
        End If
    Next i
Next j

End With

Set rng = Nothing
Set sht = Nothing
XL.ActiveWorkbook.Close (True)

XL.Quit
Set XL = Nothing

End Sub

采纳答案by Gustav

Declare and use a specific Workbookobject - as you do for Worksheetand Range, like this:

声明并使用特定的Workbook对象 - 就像对Worksheet和Range所做的那样，如下所示：

    Dim xls ? ? As Excel.Application
? ? Dim wkb ? ? As Excel.Workbook
? ? Dim wks ? ? As Excel.Worksheet
    Dim rng     As Range
? ? 
? ? Set xls = New Excel.Application
? ? Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
? ? Set wks = wkb.Worksheets(1)
    Set rng = wks.Range(<something>)
? ? 
    ' Do stuff.
? ? wks.Name = "My New Name"
    With rng
         ' Do more.
    End With

? ? wkb.Close True
? ? 
    Set rng = Nothing
? ? Set wks = Nothing
? ? Set wkb = Nothing
? ? 
? ? xls.Quit
? ? 
? ? Set xls = Nothing

Also, don't use Select, that's for visible use only. Define ranges instead.

另外，不要使用 Select，它仅用于可见用途。而是定义范围。

---

---

Cinetyk's EDIT:

Cinetyk 的编辑：

Using @Gustav 's indications, the code that does what I wanted and solves the problem is:

使用@Gustav 的指示，执行我想要的并解决问题的代码是：

Sub changeXLcolumnFormatting()

Dim XL As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim rng As Range

Set XL = New Excel.Application
XL.Visible = False
XL.DisplayAlerts = False

Set wkb = XL.Workbooks.Open("C:\Users0344\Desktop\rawDataTest.XLSX")
Set sht = wkb.Worksheets(1)

Dim i As Integer, j As Integer

field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
end_of_table = sht.UsedRange.Columns.Count

For j = 0 To UBound(field_names)
    For i = 1 To end_of_table
        Set rng = sht.Cells(1, i)
        If InStr(rng.Text, field_names(j)) > 0 Then
            sht.Columns(i).NumberFormat = "yyyy-mm-dd  HH:MM:ss"
        End If
    Next i
Next j

wkb.Close (True)
Set rng = Nothing
Set sht = Nothing

XL.Quit
Set XL = Nothing

End Sub

回答by Vityata

Here is a fancy way to solve that problem - using with new Excel.Application:

这是解决该问题的一种奇特方法 - 使用with new Excel.Application：

Option Compare Database
Option Explicit

Public Sub TestMe()

    Dim wkb     As Object
    Dim wks     As Object

    With New Excel.Application

        Set wkb = .Workbooks.Open("C:\Users\vityata\Desktop\myTest.xlsx")
        Set wks = wkb.Worksheets(1)

        wkb.Close True

        Set wks = Nothing
        Set wkb = Nothing
        .Quit

    End With

End Sub

In C# this is a standard with Using, in VBA very few people use it - I have never seen it production code.

在 C# 中这是一个标准Using，在 VBA 中很少有人使用它 - 我从未见过它生产代码。

---

---

Cinetyk's EDIT:

Cinetyk 的编辑：

Using Vityata's indications, the code that works as I intended is:

使用 Vityata 的指示，按我预期工作的代码是：

Option Compare Database
Option Explicit

Public Sub changeXLcolumnFormattingV2()

Dim sht As Object
Dim wkb As Object

With New Excel.Application
    .Visible = False
    .DisplayAlerts = False

    Set wkb = .Workbooks.Open("C:\Users0344\Desktop\rawDataTESTING.XLSX")
    Set wks = wkb.Worksheets(1)

    field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
    end_of_table = wks.UsedRange.Columns.Count

    For j = 0 To UBound(field_names)
        For i = 1 To end_of_table
            Set rng = wks.Cells(1, i)
            If InStr(rng.Text, field_names(j)) > 0 Then
                wks.Columns(i).NumberFormat = "yyyy-mm-dd  HH:MM:ss"
            End If
        Next i
    Next j

    wkb.Close True

    Set wks = Nothing
    Set wkb = Nothing
    .Quit

End With

End Sub