Excel 应用程序未从 Outlook VBA 函数关闭

Question

我正在 Outlook VBA 中为自己编写一种自行开发的票务系统，并且使用 Excel 来存储所有持久数据。我有一个用 Outlook 编写的函数，用于从 .csv 中获取一些数据并将其返回。这一切都工作正常，但在我关闭工作簿、退出应用程序并将应用程序设置为空之后，我仍然有一个 Excel 进程在运行!这是我的代码:

Private Function GetNewTicketNumber() As Integer
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    Dim FileStr As String
    Dim NumberBook As Workbook
    Dim TheRange As Range
    FileStr = "C:\OMGITSAPATH.csv"
    Set NumberBook = Workbooks.Open(FileStr)
    Set TheRange = NumberBook.Worksheets(1).Range("A1")
    GetNewTicketNumber = TheRange.Value
    TheRange.Value = TheRange.Value + 1
    NumberBook.Save
    NumberBook.Close
    xlApp.Quit
    With xlApp
        .Visible = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With

    Set xlApp = Nothing
End Function

我在这里做错了什么吗？我的问题类似于here ，但我禁用了 DisplayAlerts...我该如何解决此问题？

Answer 1

尝试完全限定对 Excel 的引用，来自 xl_doesnt_quit

这里提出的问题正是您所遇到的。这条线
范围(“a1”).Value = 范围(“a1”).Value + 1
让 xl 实例保持打开状态

The most common cause of the problem is a 'global' reference to the automated application. Unfortunately, under some circumstances it is possible to directly refer to an entity (property/method/object) of the automated object. This reference effectively is global to the calling application. Hence, the reference remains in place as long as the calling program is active. Consequently, the operating system will not end the automated application while the caller is active.

重新剪切下面的代码(也使用后期绑定(bind) - 排除了不合格的可能性)。

请更改您的路径以适应。

代码

  Private Function GetNewTicketNumber() As Long
    Dim xlApp As Object
    Dim objWB As Object
    Dim objWs As Object
    Dim FileStr As String

    FileStr = "C:\temp\test.xlsx"

    Set xlApp = CreateObject("excel.application")

    With xlApp
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    Set objWB = xlApp.Workbooks.Open(FileStr)
    Set objWs = objWB.Sheets(1)
    GetNewTicketNumber = objWs.Range("A1")
    objWs.Range("A1") = objWs.Range("A1") + 1

    objWB.Save
    objWB.Close

    Set objWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Function