VBA跨Word与Excel调用关闭已打开文档报文件名错误求助

问题背景 #

• 现有一套VBA自动化流程：打开Word文档后关闭Excel，通过Word内VBA重新启动Excel，将用户数据复制到新工作表作为表单数据源，流程主体运行正常，但任务完成后关闭Word文档环节报错。
• 需求是回到Excel环境后关闭对应的源Word文档，但所有引用该文档的操作均返回bad file name（文件名错误）报错，已确认文件路径完全正确，已知跨程序打开的文档无法通过常规引用方式调用，代码中已做隐私处理隐去filePath变量实际值。

原有问题代码 #

Word端初始执行代码 #

Sub sendTableToExcel()
 
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim ws As Worksheet
   
    Dim doc As Document
    Dim tbl As Table

   
    Set doc = ThisDocument
   
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWb = xlApp.Workbooks.Open(filePath)
   
    Set ws = Sheets.Add
    ws.Name = "temp"
   
    
    Set tbl = doc.Tables(1)
    tbl.Range.Copy
    xlWb.Worksheets(ws.Name).PasteSpecial wdPasteText
    ws.Visible = False
   
    xlWb.Application.Run "pasteCopiedValuesFromRequestDocs" 
    ' 标记位置：此处为尝试触发关闭文档逻辑的调用点
    xlWb.Application.Run "openRequestLanding", "Casual"
   
    Set xlWb = Nothing
    Set xlApp = Nothing
   
    Set tblRange = Nothing
    Set tbl = Nothing
    Set doc = Nothing
  
    
End Sub

Excel端被调用子程序代码 #

Public Sub openRequestLanding(requestType As String)
   
    Dim wdApp As Word.Application
    Dim doc As Word.Document
    
    ' 核心错误逻辑：新建独立Word进程，并非运行原有代码的Word实例
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set doc = wdApp.Documents(filePath)
   
    doc.Close SaveChanges:=wdDoNotSaveChanges
   
    Set wdApp = Nothing
    Set doc = Nothing
   
    RequestLanding.RequestTypeBox.Value = requestType
    RequestLanding.Show
   
 
End Sub

报错根因 #

Excel端代码中CreateObject("Word.Application")会创建一个全新的独立Word进程，和之前打开目标文档、运行VBA代码的Word实例完全隔离，新进程内没有打开目标文档，自然返回bad file name错误。
原Word端代码还存在隐性bug：Set ws = Sheets.Add未指定所属工作簿，跨程序调用时会默认在当前活动工作簿创建工作表，极易出现工作表归属错误。

修复方法 #

无需新建Word进程，直接将当前打开的Word文档对象作为参数从Word端传递到Excel宏中操作即可，步骤如下：

• 修改Word端宏调用代码，将当前文档对象作为参数传入，同时修复工作表创建的隐性bug
• 修改Excel端宏定义，接收传入的Word文档对象，直接调用关闭方法，移除新建Word实例的冗余逻辑

修复后Word端代码 #

Sub sendTableToExcel()
 
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim ws As Worksheet
   
    Dim doc As Document
    Dim tbl As Table

   
    Set doc = ThisDocument
   
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWb = xlApp.Workbooks.Open(filePath)
   
    ' 修复：明确指定在目标工作簿中新建工作表
    Set ws = xlWb.Sheets.Add
    ws.Name = "temp"
   
    
    Set tbl = doc.Tables(1)
    tbl.Range.Copy
    ' 跨程序调用时wdPasteText为Word常量，若提示未定义可直接替换为对应数值2
    xlWb.Worksheets(ws.Name).PasteSpecial 2
    ws.Visible = False
   
    xlWb.Application.Run "pasteCopiedValuesFromRequestDocs" 
    ' 修复：将当前Word文档对象作为参数传入Excel宏
    xlWb.Application.Run "openRequestLanding", "Casual", doc
   
    Set xlWb = Nothing
    Set xlApp = Nothing
   
    Set tblRange = Nothing
    Set tbl = Nothing
    Set doc = Nothing
  
    
End Sub

修复后Excel端代码 #

' 修复：新增参数接收传入的Word文档对象
Public Sub openRequestLanding(requestType As String, sourceDoc As Word.Document)
   
    ' 直接操作传入的文档对象，无需新建Word实例
    sourceDoc.Close SaveChanges:=wdDoNotSaveChanges
   
    RequestLanding.RequestTypeBox.Value = requestType
    RequestLanding.Show
   
 
End Sub

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