Access VBA操作Excel后进程残留问题及解决实践

一、Excel进程残留问题：错误与正确写法对比 #

执行Access VBA与Excel交互的代码后，Excel进程常会残留于内存中，锁定输出文件。网上相关文章众多，但缺少Access VBA场景下的错误与解决方案对比实例。以下适配Access窗体命令按钮的测试代码，可通过注释/取消注释代码行复现问题：

Private Sub cmdTest03_Click()

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.WorkBook
    Dim xlSheet As Excel.Worksheet

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets("Sheet1")

    ' **********************************************************
    ' 取消注释以下代码，执行后Excel.exe会残留内存
    '
    ' xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello"

    ' **********************************************************
    ' 使用以下代码，执行后Excel.exe可正常退出
    '
    xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(10, 2)).Value = "Hello"
    
    xlBook.Saved = True
    Set xlSheet = Nothing
    Set xlBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing

    Beep

End Sub

核心差异：错误写法中Cells未指定所属工作表，导致隐式依赖Excel全局对象，形成未释放的引用；正确写法显式通过xlSheet.Cells指定归属，彻底避免残留引用。

二、现有代码整合解决方案 #

将上述方案整合到业务代码时，尝试了Stack Overflow及专业书籍中的各类方案，耗时数周。原代码因调用FreezeTopRowPane子过程出现进程残留，替换为直接操作Excel窗口的代码后，问题得到解决。以下是优化后的可行代码：

' ****************************************************
' 优化后的[cmdTest01_Click]测试代码
'
Public Sub cmdTest01_Click()

Dim s01MainF        As String
Dim sTableName      As String
Dim sSheet          As String
Dim sTemp01         As String

s01MainF = "frmzzzX"
sTableName = "zzz_p1r4c3_TEST"
sTemp01 = ""
sTemp01 = Application.CurrentProject.Path & "\a01_Excel\"
sTemp01 = sTemp01 & sTableName & ".xls"
gsPathFile = sTemp01 & ""

If FileExists(gsPathFile) Then
   DoEvents
   Call DeleteFile(gsPathFile)
   DoEvents
End If

DoEvents
DoEvents
DoCmd.OutputTo ObjectType:=acOutputTable, _
            ObjectName:=sTableName, _
            OutputFormat:=acFormatXLS, _
            Outputfile:=gsPathFile
DoEvents
DoEvents

Me.txtTableName.Value = " " & sTableName

Dim appExcel         As Excel.Application
Dim WorkBook         As Excel.WorkBook
Dim Wsheet           As Excel.Worksheet

Set appExcel = CreateObject("Excel.Application")
Set WorkBook = appExcel.Workbooks.Open(gsPathFile)
Set Wsheet = WorkBook.Worksheets(sTableName)

appExcel.Visible = False

WorkBook.Worksheets(1).Range("A1").Activate
WorkBook.Worksheets(1).Activate

With appExcel.ActiveWindow
     .FreezePanes = False
     .SplitColumn = 0             '设置无列拆分
     .SplitRow = 1                '设置拆分行
     .FreezePanes = True
End With

appExcel.DisplayAlerts = False
WorkBook.SaveAs gsPathFile
appExcel.DisplayAlerts = True

WorkBook.Saved = True
WorkBook.Close SaveChanges:=False

Set Wsheet = Nothing
Set WorkBook = Nothing

appExcel.Quit

Set appExcel = Nothing
DoEvents

GoTo cmdTest01_Click_End   '跳过OLE相关代码

DoEvents
Set ctl = Me!OLEUnbound0
With ctl
  '启用控件
  .Enabled = True
  '取消锁定
  .Locked = False
  '仅允许嵌入对象
  .OLETypeAllowed = acOLEEmbedded
  '指定OLE服务器及对象类型
  .Class = "Excel.Sheet"
  '指定嵌入文件路径
  .SourceDoc = gsPathFile
  '创建嵌入对象
  .Action = acOLECreateEmbed
  '设置激活方式为编辑
  .Verb = acOLEVerbShow
  '激活对象
  .Action = acOLEActivate
End With

cmdTest01_Click_End:

End Sub

优化点：移除了可能导致隐式引用的子过程调用，所有Excel对象操作均显式归属到appExcel或指定的工作簿/工作表，确保所有引用可被正确释放。

三、待解决问题 #

目前仍需解决Access窗体中使用OLE控件显示Excel表格的问题，当前代码中OLE相关逻辑被跳过，执行后无法正常嵌入并显示目标Excel文件。

内容的提问来源于stack exchange，提问作者Edward Z.