excel - 如何在 MS Access VBA 中管理 MS Excel 应用程序进程

Question

我有以下代码，该代码会导致恶意 Excel 进程，从而干扰后续执行。我想知道我做错了什么导致这个开放。

但是，如果我设置 xl.visible = true，则当应用程序关闭时该进程将被终止。

我认为问题集中在

Set wb = xl.Workbooks.Add(templatePath)

应用程序运行后，如果我打开创建的文件之一，它也会打开模板工作簿。这似乎是挂起的过程。

Public Function CreateAYRs()

'This process is known to leave a hanging excel process open after execution.

'database objects
Dim rsHeader As DAO.Recordset
Dim rsDetail As DAO.Recordset
Dim db As DAO.Database
'Excel objects
Dim xl As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim templatewb As Workbook
'paths
Dim templatePath As String
Dim outboxPath As String

On Err GoTo ErrHandler

templatePath = "\\ahmtroy03\sppcsharedfiles\6290 Procurement Operations\Systems\Applications\AYR\AYR Form.xlsx"
outboxPath = "\\ahmtroy03\sppcsharedfiles\6290 Procurement Operations\_Public\Data\AYR\Sent\"

Set db = CurrentDb()

Set rsHeader = db.OpenRecordset("SELECT * FROM qryAYRtoSendHeader")

If Not rsHeader.BOF And Not rsHeader.EOF Then
    'there is a PO, create the parent application
    Set xl = New Excel.Application
    'xl.Visible = True

    rsHeader.MoveFirst
    While (Not rsHeader.EOF)
        'There will be a new workbook for each PO
        Set wb = xl.Workbooks.Add(templatePath)
        'Get all the lines
        Set rsDetail = db.OpenRecordset("SELECT [Due Date],[Model],[Part Number],[Part Desc] FROM qryAYRtoSendDetail WHERE [VC] In('" & rsHeader![VC] & "')")
        If Not rsDetail.BOF And Not rsDetail.EOF Then
            'Move the data to the spreadsheet
            wb.Sheets(2).Range("A2").CopyFromRecordset rsDetail
        End If
        'cleanup
        rsDetail.Close
        Set rsDetail = Nothing

        'Save the form, supress overwrite warnings
        wb.Application.DisplayAlerts = False
        wb.SaveAs outboxPath & "AHM AYR " & rsHeader![VC] & " - " & Format(Date, "mm-dd-yy"), FileFormat:=51
        wb.Application.DisplayAlerts = True

        'email the form

        wb.Close False
        Set wb = Nothing
        'move to the next PO
        rsHeader.MoveNext
    Wend
xl.Quit
Set xl = Nothing
End If
'cleanup
rsHeader.Close
Set rsHeader = Nothing
Set db = Nothing

Exit Function
ErrHandler:
MsgBox Err.Description

End Function

Answer 1

提供了很多非常有帮助和创造性的建议，但解决方案实际上是大错特错。

我做了一些更多的研究，发现某个地方表明该问题可能是由 Excel COM 插件造成的。我禁用了所有加载项(其中一个是公司发行的)，并且挂起的 excel 进程问题完全停止了。