Word文档ChartData窗口无法关闭：多次运行宏致Excel崩溃求助

我之前也踩过这个坑，尤其是处理包含不同类型图表的Word文档时，Word的ChartData对象经常会“赖着不走”，残留的Excel窗口/进程最终导致崩溃。结合你的场景，分享几个实测有效的解决方法：

1. 显式管理Excel实例，避免自动创建的进程残留 #

不要依赖Word自动生成Excel实例，而是手动创建/获取Excel应用对象，这样能完全掌控它的生命周期：

Sub UpdateChartsAndCleanup()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim cht As Word.Chart
    Dim excelApp As Excel.Application
    Dim excelWB As Excel.Workbook
    
    Set wdApp = Word.Application
    Set wdDoc = wdApp.ActiveDocument
    
    ' 提前创建/获取Excel实例，后续所有图表复用这个实例
    On Error Resume Next
    Set excelApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set excelApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    excelApp.Visible = False ' 后台运行，避免弹出窗口
    
    For Each cht In wdDoc.InlineShapes
        If cht.HasChart Then
            With cht.Chart
                If .ChartData.IsLinked Then
                    ' 链接图表直接刷新，无需激活ChartData
                    .Refresh
                Else
                    .ChartData.Activate
                    Set excelWB = .ChartData.Workbook
                    
                    ' 这里写入你的数据更新逻辑
                    ' excelWB.Sheets(1).Range("A1").Value = "Updated Content"
                    
                    ' 尝试正常关闭工作簿，处理不支持关闭的异常
                    On Error Resume Next
                    excelWB.Close SaveChanges:=True
                    If Err.Number <> 0 Then
                        ' 遇到不支持关闭属性的图表，强制释放对象
                        Set excelWB = Nothing
                        ' 可选：用API强制关闭ChartData窗口
                        CloseChartDataWindow
                    End If
                    On Error GoTo 0
                End If
            End With
        End If
    Next cht
    
    ' 彻底清理Excel实例：关闭所有残留工作簿并退出
    Do While excelApp.Workbooks.Count > 0
        excelApp.Workbooks(1).Close SaveChanges:=False
    Loop
    excelApp.Quit
    
    ' 显式释放所有COM对象
    Set excelWB = Nothing
    Set excelApp = Nothing
    Set cht = Nothing
    Set wdDoc = Nothing
    Set wdApp = Nothing
    
    ' 强制触发垃圾回收，释放残留COM引用
    Call TriggerGC
End Sub

' 辅助函数：用Windows API强制关闭ChartData窗口（需放在模块顶部）
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
Private Const WM_CLOSE = &H10

Sub CloseChartDataWindow()
    Dim hwnd As LongPtr
    ' 匹配Excel的ChartData窗口标题（可根据系统语言调整）
    hwnd = FindWindow("XLMAIN", "Chart in Microsoft Word")
    If hwnd <> 0 Then
        SendMessage hwnd, WM_CLOSE, 0, 0
    End If
End Sub

Sub TriggerGC()
    ' 强制VBA释放未回收的COM对象
    Dim obj As Object
    On Error Resume Next
    For Each obj In VBA.Collection
        Set obj = Nothing
    Next obj
    DoEvents
End Sub

2. 针对特殊图表的容错处理 #

有些旧版本Excel创建的图表、第三方插件生成的图表确实不支持ChartData.Workbook.Close，可以通过以下方式规避：

• 提前用TypeName(.ChartData.Workbook)判断是否为正常Excel工作簿对象，异常则跳过关闭步骤
• 用On Error Resume Next包裹关闭逻辑，避免单个图表的错误导致整个宏中断

3. 收尾清理残留Excel进程 #

如果还是有漏网的无窗口Excel进程，可在宏末尾调用以下代码强制清理：

Sub KillOrphanedExcel()
    Dim objWMIService As Object
    Dim colProcesses As Object
    Dim objProcess As Object
    
    Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
    Set colProcesses = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'EXCEL.EXE'")
    
    For Each objProcess In colProcesses
        ' 关闭后台运行的无窗口Excel进程
        If InStr(objProcess.CommandLine, "Visible=False") > 0 Then
            objProcess.Terminate
        End If
    Next objProcess
    
    Set objProcess = Nothing
    Set colProcesses = Nothing
    Set objWMIService = Nothing
End Sub

关键注意事项 #

• 永远显式释放所有COM对象（Set xxx = Nothing），不要依赖VBA自动回收
• 避免在循环中反复创建Excel实例，尽量复用同一个实例
• 链接图表无需激活ChartData，直接调用.Refresh即可，减少窗口创建

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