Excel 应用程序未从 Outlook VBA 功能关闭 [英] Excel application not closing from Outlook VBA function

问题描述

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

I'm writing a sort of homegrown ticketing system for myself in Outlook VBA, and I'm using Excel to store all the persistant data. I have a function written in Outlook to get some data from the .csv and return it. This is all working fine, but after I close the workbook, quit the application, and set the app to nothing I still have an Excel process running! Here is my code:

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

我在这里做错了什么吗?我的问题类似于这里，但我已禁用 DisplayAlerts... 我该怎么做才能解决此问题?

Is there something that I'm doing wrong here? My problem is similar to the one here, but I have disabled DisplayAlerts... What can I do to fix this problem?

推荐答案

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

Try fully qualifying your references to Excel, from xl_doesnt_quit

这里提出的问题正是您遇到的问题.这一行
Range("a1").Value = Range("a1").Value + 1
让 xl 实例保持打开状态

The problem presented here is exactly what you have. This line
Range("a1").Value = Range("a1").Value + 1
leave the xl instance open

问题的最常见原因是对自动化应用程序的全局"引用.不幸的是，在某些情况下，可以直接引用自动化对象的实体(属性/方法/对象).该引用实际上对调用应用程序是全局的.因此，只要调用程序处于活动状态，引用就会保持原位.因此，当调用者处于活动状态时，操作系统不会结束自动应用程序.

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.

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

Re-cut code below (which also uses late binding - which rules out the unqualified possibility).

请改变你的道路以适应.

Pls change you path to suit.

代码

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

    FileStr = "C:	emp	est.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

这篇关于Excel 应用程序未从 Outlook VBA 功能关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！