使用VBA获取多Excel实例时导出工作簿关闭后VB项目残留问题

问题根源 #

残留的核心原因是代码中持有了大量跨实例的Workbook、Application对象强引用未完全释放，导致Excel无法卸载对应工作簿的VBA项目。尤其是openWbs集合全局持有了所有枚举到的工作簿引用，即使工作簿关闭，引用计数不归零就会出现残留。

修复方案 #

1. 主过程修改 #

新增对导出工作簿所属实例的释放逻辑，无其他工作簿时直接退出实例，从根源清理资源：

Sub getQlikDataToExcel()
    Dim qlikTableName As String
    Dim qD As QlikView.Document
    Dim qApp As New QlikView.Application
    Dim srcWb As Workbook
    Dim srcApp As Application
    
    Set qD = qApp.ActiveDocument
    qlikTableName = "Document\CH78"
    Set srcWb = tableToExcel(qlikTableName, qD)
    
    If Not srcWb Is Nothing Then
        Set srcApp = srcWb.Application
        ' 此处放置你原有数据复制逻辑
        ' ......
        
        srcWb.Close False
        ' 所属实例无其他工作簿则直接退出
        If srcApp.Workbooks.Count = 0 Then
            srcApp.Quit
        End If
        Set srcWb = Nothing
        Set srcApp = Nothing
    End If
    
    Set qD = Nothing
    Set qApp = Nothing
    ' 强制清理COM引用残留
    CollectGarbage
End Sub

2. tableToExcel函数修改 #

新增所有临时集合、对象的释放逻辑，尤其是openWbs集合的完全清理：

Function tableToExcel(tName As String, qD As QlikView.Document, Optional waitIntervalSecs As Long = 180) As Workbook
    Dim success As Boolean, wbNew As Boolean
    Dim timeout As Date
    Dim openWbs As New Collection
    Dim wb As Workbook, openWb As Workbook
    Dim xlApp As Application
    Dim instColl As Collection
         
    ' 收集初始已打开工作簿
    Set instColl = xlInst.GetExcelInstances()
    For Each xlApp In instColl
        For Each wb In xlApp.Workbooks
            openWbs.Add wb
        Next wb
    Next xlApp
    Set xlApp = Nothing
    Set instColl = Nothing
    
    wbNew = False
    success = False
    timeout = DateAdd("s", waitIntervalSecs, Now())
    
    DoEvents
    qD.GetSheetObject(tName).SendToExcel
    
    Do
        DoEvents
        Set instColl = xlInst.GetExcelInstances()
        For Each xlApp In instColl
            For Each wb In xlApp.Workbooks
                If InStr(1, wb.Name, tName) > 0 Or _
                   InStr(1, wb.Name, Replace(tName, "Document\", "")) > 0 Or _
                   InStr(1, wb.Name, Replace(tName, "Server\", "")) > 0 Then
                    
                    wbNew = True
                    For Each openWb In openWbs
                        If wb Is openWb Then
                            wbNew = False
                            Exit For
                        End If
                    Next openWb
                    
                    If wbNew Then
                        Set tableToExcel = wb
                        success = True
                        Exit For
                    End If
                End If
            Next wb
            If success Then Exit For
        Next xlApp
        ' 每次循环清理临时引用
        Set wb = Nothing
        Set xlApp = Nothing
        Set instColl = Nothing
    Loop Until success Or Now() > timeout
    
    ' 关键：清空openWbs所有持有的工作簿引用
    For Each openWb In openWbs
        Set openWb = Nothing
    Next
    Set openWbs = Nothing
    Set wb = Nothing
    Set xlApp = Nothing
    Set instColl = Nothing
    
    If Not success Then Set tableToExcel = Nothing
End Function

3. xlInst模块无需修改 #

原有枚举逻辑本身没有问题，只需保证每次调用返回的Collection用完立即释放即可。

注意事项 #

• 所有跨实例的Workbook、Application对象不要存储在全局变量中，临时引用用完立即设为Nothing
• CollectGarbage为VBA内置方法，专门用于清理未释放的COM引用，在过程末尾调用可大幅降低残留概率
• 若Qlik导出固定为新实例，每次关闭工作簿后直接退出对应实例，可完全避免VBA项目残留问题

内容的提问来源于stack exchange，提问作者Professor Pantsless