Excel.Application Object .Quit 使 EXCEL.EXE 运行 [英] Excel.Application Object .Quit leaves EXCEL.EXE running

问题描述

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

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.

我已经完成了另一个 Sub，它只是在磁盘上打开一个 Excel 文件并将所有列的宽度更改为自动宽度"，然后关闭，这工作正常并且不会让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.

我做错了什么?是否可以在 VBA 中确保EXCEL.EXE"正确退出?

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

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

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.

感谢您阅读本文.

代码如下:

Sub changeXLcolumnFormatting()

Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = False
XL.DisplayAlerts = False
XL.Workbooks.Open "C:Users640344Desktop
awDataTest.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

推荐答案

声明并使用特定的 Workbook 对象 - 就像您对 Worksheet 和 Range<所做的一样/em>，像这样:

Declare and use a specific Workbook object - as you do for Worksheet and Range, like this:

    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:	estworkbook1.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

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

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

Cinetyk 的

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

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

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:Users640344Desktop
awDataTest.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

这篇关于Excel.Application Object .Quit 使 EXCEL.EXE 运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！