如何通过VBA在Excel实例中连接SAP生成的无路径打开工作簿并完成保存

我之前处理过SAP导出Excel的类似场景，这种情况确实比较棘手——SAP生成的Excel工作簿通常处于未保存的临时状态（无Path属性），而且是独立的Excel实例，常规的GetObject方法因为依赖文件路径所以完全失效。下面是我验证过的可行方案：

核心思路 #

通过Windows API枚举所有运行中的Excel窗口，获取对应的Excel.Application实例，再遍历每个实例下的工作簿，筛选出SAP生成的目标工作簿后执行SaveAs操作。

具体实现代码 #

首先在VBA模块中添加以下API声明和类型定义：

Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    
Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" _
    (ByVal hWnd As LongPtr, lpdwProcessId As Long) As Long
    
Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" _
    (ByVal hWnd As LongPtr, ByVal dwId As Long, riid As GUID, ppvObject As Object) As Long
    
Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
    (ByVal hWnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long

Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type

接着写一个函数获取所有Excel实例：

Function GetAllExcelInstances() As Collection
    Dim colInstances As New Collection
    Dim hWndDesk As LongPtr, hWndExcel As LongPtr
    Dim xlApp As Object
    Dim guid As GUID
    Dim windowTitle As String * 256
    
    ' 初始化Excel对象的GUID
    With guid
        .Data1 = &H00020400
        .Data2 = &H0
        .Data3 = &H0
        .Data4(0) = &HC0
        .Data4(1) = &H0
        .Data4(2) = &H0
        .Data4(3) = &H0
        .Data4(4) = &H0
        .Data4(5) = &H0
        .Data4(6) = &H0
        .Data4(7) = &H46
    End With
    
    ' 遍历所有Excel窗口
    hWndDesk = FindWindowEx(0&, 0&, "Progman", vbNullString)
    hWndExcel = FindWindowEx(hWndDesk, 0&, "Excel7", vbNullString)
    
    Do While hWndExcel <> 0
        ' 获取窗口标题（可选，用于更精准判断）
        GetWindowText hWndExcel, windowTitle, 256
        
        ' 获取对应的Excel.Application实例
        If AccessibleObjectFromWindow(hWndExcel, &HFFFFFFF0, guid, xlApp) = 0 Then
            ' 避免重复添加同一实例
            Dim isDuplicate As Boolean
            isDuplicate = False
            Dim existingApp As Object
            For Each existingApp In colInstances
                If existingApp Is xlApp Then
                    isDuplicate = True
                    Exit For
                End If
            Next existingApp
            
            If Not isDuplicate Then
                colInstances.Add xlApp
            End If
        End If
        
        hWndExcel = FindWindowEx(hWndDesk, hWndExcel, "Excel7", vbNullString)
    Loop
    
    Set GetAllExcelInstances = colInstances
End Function

最后是主程序，筛选目标工作簿并保存：

Sub SaveSAPGeneratedWorkbook()
    Dim colInstances As Collection
    Dim xlApp As Object
    Dim targetWB As Object
    
    Set colInstances = GetAllExcelInstances()
    
    For Each xlApp In colInstances
        For Each targetWB In xlApp.Workbooks
            ' 筛选条件：无路径（未保存）+ 窗口标题包含SAP特征（可根据实际调整）
            If targetWB.Path = "" And InStr(1, xlApp.Caption, "SAP", vbTextCompare) > 0 Then
                ' 执行SaveAs，这里替换成你的目标路径
                targetWB.SaveAs "C:\Tmp\SAP_Export_Saved.xlsx", FileFormat:=51 ' 51对应xlsx格式
                MsgBox "SAP导出的工作簿已成功保存！"
                Exit Sub
            End If
        Next targetWB
    Next xlApp
    
    MsgBox "未找到目标SAP生成的Excel工作簿！"
End Sub

注意事项 #

1. 宏权限设置：运行代码的Excel需要启用宏，并且在「Excel选项→信任中心→信任中心设置→宏设置」中勾选「信任对VBA工程对象模型的访问」。
2. 筛选条件调整：如果SAP导出的窗口标题没有"SAP"关键词，可以换成工作簿名称（比如默认的"Book1"），甚至检查工作簿内的特定单元格内容来精准定位。
3. 兼容性：代码添加了PtrSafe关键字，兼容32位和64位Excel环境。

内容的提问来源于stack exchange，提问作者Radosław Poprawski