SAP表导出至电子表格时弹窗中断宏，寻求绕过方案

我尝试从SAP中提取表格并保存为电子表格，但运行过程中出现弹窗导致宏暂停。需说明的是，我始终保持PERSONAL.xlsb工作簿处于打开状态。以下是相关代码，希望能获取绕过该弹窗且不中断代码运行的方法。

Sub SAP_TABLE()

Session.findById("wnd[0]").maximize
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/NZSE16"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/ctxtI_TABLE").Text = MainWS ' main table
Session.findById("wnd[0]/usr/ctxtI_TABLE").caretPosition = 4
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/tbar[1]/btn[8]").press
Session.findById("wnd[0]/mbar/menu[3]/menu[2]").Select
Session.findById("wnd[1]/tbar[0]/btn[14]").press
Session.findById("wnd[1]/usr/chk[2,5]").Selected = False
Session.findById("wnd[1]/usr/chk[2,15]").Selected = True
Session.findById("wnd[1]/usr/chk[2,10]").Selected = True
Session.findById("wnd[1]/usr/chk[2,10]").SetFocus
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Session.findById("wnd[0]/usr/ctxtI1-LOW").Text = "GR40"
Sheets(CfgWs).Select  ' gia na parei tis imerominies
On Error Resume Next
Session.findById("wnd[0]/usr/ctxtI2-LOW").Text = Range("c5") '"01.02.2023"
Session.findById("wnd[0]/usr/ctxtI2-HIGH").Text = Range("c6") '"28.02.2023"
Session.findById("wnd[0]/usr/ctxtI2-HIGH").SetFocus
Session.findById("wnd[0]/usr/ctxtI2-HIGH").caretPosition = 10
Session.findById("wnd[0]").sendVKey 0
'load table
Session.findById("wnd[0]/tbar[1]/btn[8]").press
'create spreadsheet
Session.findById("wnd[0]").sendVKey 43
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Sheets(CfgWs).Activate
Session.findById("wnd[1]/usr/ctxtDY_PATH").Text = Range("C10")
Session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = RefWb
Session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 8
Session.findById("wnd[2]/tbar[0]/btn[11]").press
Session.findById("wnd[1]/tbar[0]/btn[11]").press

'============================================================================
' waiting for SAP download the spreadsheet and open it
'============================================================================
Const TimeOut = 240   ' 2 minutes.
Const Exportfile = "DOWNLOAD.XLSX"

Dim timeCounter As Long
For timeCounter = 1 To TimeOut Step 1
    '======================
    DoEvents  ' gia na kanei paralliles ergasies otan doulebei.
    '======================
    If ActiveWorkbook.Name = Exportfile Then
        'MsgBox Exportfile & " downloaded in " & timeCounter & "s."
        Workbooks(RefWb).Activate
            RefWb = ActiveWorkbook.Name
                Sheets(1).Select
                    Range("A1").Select: Selection = "Solution works..."
        Exit For
    End If
    Application.Wait Now + TimeValue("00:00:01")
Next timeCounter

If ActiveWorkbook.Name <> Exportfile Then
    MsgBox "A timeout occurred downloading " & Exportfile
End If
'=============================================================================
'here is the end of module
'=============================================================================


End Sub

针对性解决方法 #

1. 处理文件覆盖弹窗 #

如果目标路径下已有同名文件，SAP或Excel会弹出覆盖确认窗口，可提前检查并删除旧文件：

' 在设置导出路径和文件名前添加
Dim fullSavePath As String
fullSavePath = Range("C10").Value & "\" & RefWb
If Dir(fullSavePath) <> "" Then
    Kill fullSavePath
End If

2. 自动关闭SAP导出警告弹窗 #

SAP导出时可能弹出数据量、格式兼容等警告，可添加循环捕获并关闭弹窗：

' 在Session.findById("wnd[1]/tbar[0]/btn[11]").press之后添加
Do While True
    DoEvents
    On Error Resume Next
    ' 检查是否存在确认弹窗（wnd[2]为SAP常见弹窗窗口）
    If Session.findById("wnd[2]/tbar[0]/btn[11]") Is Nothing Then
        Exit Do
    Else
        Session.findById("wnd[2]/tbar[0]/btn[11]").press ' 点击确认按钮
    End If
    On Error GoTo 0
Loop

3. 避免PERSONAL.xlsb事件干扰 #

临时禁用Excel事件，防止PERSONAL.xlsb的自动事件触发弹窗，同时添加错误处理确保事件能恢复：

Sub SAP_TABLE()
    Application.EnableEvents = False
    On Error GoTo Cleanup ' 错误捕获
    
    ' 原代码内容...
    
Cleanup:
    Application.EnableEvents = True
    If Err.Number <> 0 Then
        MsgBox "宏运行出错：" & Err.Description
    End If
End Sub

4. 优化等待逻辑 #

原代码依赖ActiveWorkbook.Name判断下载状态，PERSONAL.xlsb可能干扰活动工作簿，改为直接检查目标文件是否打开：

' 替换原等待循环部分
Dim wb As Workbook
Dim timeCounter As Long
For timeCounter = 1 To TimeOut Step 1
    DoEvents
    On Error Resume Next
    Set wb = Workbooks(Exportfile)
    On Error GoTo 0
    If Not wb Is Nothing Then
        Workbooks(RefWb).Activate
        Sheets(1).Range("A1").Value = "Solution works..."
        Exit For
    End If
    Application.Wait Now + TimeValue("00:00:01")
Next timeCounter

If wb Is Nothing Then
    MsgBox "下载" & Exportfile & "超时"
End If

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