导出SAP数据在运行时无法打开工作簿

问题描述

我编写了一个代码，用于将数据从SAP导出到Excel工作簿.为此，我打开事务(在本例中为IW28)，导出文件并将其保存到指定位置.打开文件后，我想将数据从导出的工作表复制到我自己的工作表中.

I wrote a code to export data from SAP to an Excel workbook. To do that I open the transaction (IW28 in this case), export the file and save it to a specified location. When the file is opened, I want to copy the data from the exported sheet to my own sheet.

问题是我运行宏时导出的工作表无法打开.当我在代码中添加断点时，在宏停止时，导出的工作表将打开.没有此断点，仅在宏完成后才打开导出的工作表.

The problem is that the exported sheet won't open while I'm running the macro. When I add a breakpoint in my code, the exported sheet opens when the macro stops. Without this breakpoint the exported sheets only opens after the macro had finished.

'Laden van de data uit transactie IW28 naar de sheet
Dim lastcolumn As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'SAP Variant
SapVariant = "Variant"

'Transaction
Name = IW28

'Current File Location
Map = Application.ActiveWorkbook.Path

If Not IsObject(SapApp) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SapApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = SapApp.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject SapApp, "on"
End If

'Check if file is already open, if so then close the file
If IsOpen(Name & ".xlsx") = True Then Workbooks(Name & ".xlsx").Close

'Open Transaction
session.findById("wnd[0]/tbar[0]/okcd").Text = "/N" & Name
session.findById("wnd[0]").sendVKey 0

'Choose Variant
On Error Resume Next
session.findById("wnd[0]/mbar/menu[2]/menu[0]/menu[0]").Select
session.findById("wnd[1]/usr/txtV-LOW").Text = SapVariant

'Check if variant excists
If Not Err.Number = 0 Then
    VarMsgbox = MsgBox("Selecteer variant " & SapVariant & ", dubbelklik om deze te selecteren en klik dan hieronder op ok (niet eerder!)", vbOKCancel, "Selecteer variant")
    If VarMsgbox = vbCancel Then Exit Sub
Else
    session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
    session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 10
    session.findById("wnd[1]").sendVKey 0
    session.findById("wnd[1]").sendVKey 8
End If

'Execute Variant
session.findById("wnd[0]").sendVKey 8

'Select all data in SAP
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").SelectAll

'Export to Excel
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/tbar[0]/btn[0]").press

'Add filename and path
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Name & ".xlsx"
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = Map
session.findById("wnd[1]/tbar[0]/btn[11]").press

'Determine lastrow of sheet
Lastrow = Workbooks(Name & ".xlsx").Sheets("Sheet1").Range("B99999").End(xlUp).Row

ThisWorkbook.Sheets(Name).Range("A8:C99999").ClearContents

Workbooks(Name & ".xlsx").Sheets("Sheet1").Range("A2:C" & Lastrow).Copy

ThisWorkbook.Sheets(Name).Range("A8").PasteSpecial xlPasteValues

ThisWorkbook.Sheets("Start").Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True

推荐答案

您可以尝试以下程序构造:

You could try the following program construction:

Public Name as String

Sub Makro1()
 'Laden van de data uit transactie IW28 naar de sheet
 Dim lastcolumn As Long

 . . .

 session.findById("wnd[1]/tbar[0]/btn[11]").press
 call Makro2
End Sub

Sub Makro2()
 'Determine lastrow of sheet
 Lastrow = Workbooks(Name &    .xlsx").Sheets("Sheet1").Range("B99999").End(xlUp).Row

 ThisWorkbook.Sheets(Name).Range("A8:C99999").ClearContents

 Workbooks(Name & ".xlsx").Sheets("Sheet1").Range("A2:C" & Lastrow).Copy

 ThisWorkbook.Sheets(Name).Range("A8").PasteSpecial xlPasteValues

 ThisWorkbook.Sheets("Start").Activate

 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
End Sub

这篇关于导出SAP数据在运行时无法打开工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！