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

问题描述

我正在Windows 10上的MS Access 2013中工作，我只是试图编写一个Sub，该Sub在磁盘上打开Excel文件，更改列格式和某些列，然后保存并退出. Sub运行并按预期运行，但问题是，即使在.Quit命令之后，"EXCEL.EXE"仍保持运行，并且对该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:\Users\640344\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

推荐答案

声明并使用特定的 Workbook 对象-就像对 Worksheet 和 Range ，就像这样:

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

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:\Users\640344\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

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