I received from You so many questions about this, so I decided to investigate the issue. In this article I’m going to show You how to save as and open Excel spreadsheet during execution of SAP scripting code.
So I recorded the actions from the previous article – go to se16n transaction, choose MVKE table, type random product and confirm. Later choose Save As from the list and select Spreadsheet.
Then leave the default location, change the name of the file to export1.xlsx and click Generate.
The code for described actions above looks like this:
session.findById("wnd[0]/tbar[0]/okcd").Text = "se16n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtGD-TAB").Text = "MVKE"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/ctxtGS_SELFIELDS-LOW[2,1]").Text = "hek102"
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "export1.XLSX"
session.findById("wnd[1]").sendVKey 0I don’t paste the connection code here, to make this shorter. However if You are interested in how to connect to SAP using VBA check this article.
After last line the export Excel file should come up, so You want to set it to the object variable.
Dim wb As Workbook, wbExport As Workbook
For Each wb In Application.Workbooks
If InStr(1, wb.Name, "export") > 0 Then
Set wbExport = wb
End If
Next wbUnfortunately, this loop is not finding anything than ThisWorkbook.
Solution trials
It was weird for me, because I remember the times when this was not a problem at all. Export spreadsheet was opening always, without any issue.
So I thought to myself, that maybe it needs some time, to pop up. So after Generate button click I put the 5 second pause.
Application.Wait now + TimeValue("0:00:05")Unfortunately that didn’t work.
I tried to extend this to even 15 seconds, but all for nothing.
I also tried with loop, pause for a second and wait to set the new Excel workbook, but it never came up.
All in all, I got a long journey search through the Internet and finally got up with DoEvents. It was not working, even though I put that in the code in so many places, but I tried that also with the loop and… that was it!
The key to succed!
So before all the SAP scripting code I counted the amount of opened workbooks and set it to the variable.
Dim appWorkbookCount As Long
appWorkbookCount = Application.Workbooks.CountThen, just after the Generate button click, I put the Do While loop with key DoEvents to make the export file to pop up!
Do
DoEvents
Loop While appWorkbookCount = Application.Workbooks.CountThanks to that loop, the SAP export comes up and You can easily set this workbook to the variable.
Dim wb As Workbook , wbExport As Workbook
For Each wb In Application.Workbooks
If InStr(1, wb.Name, "export") > 0 Then
Set wbExport = wb
End If
Next wbSo the whole code can look like this.
Dim appWorkbookCount As Long
appWorkbookCount = Application.Workbooks.Count
session.findById("wnd[0]/tbar[0]/okcd").Text = "se16n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtGD-TAB").Text = "MVKE"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/ctxtGS_SELFIELDS-LOW[2 ,1]").Text = "hek102"
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlRESULT_LIST/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "export1.XLSX"
session.findById("wnd[1]").sendVKey 0
Do
DoEvents
Loop While appWorkbookCount = Application.Workbooks.Count
Dim wb As Workbook, wbExport As Workbook
For Each wb In Application.Workbooks
If InStr(1, wb.Name, "export") > 0 Then
Set wbExport = wb
End If
Next wbIt took me a quite some time of search and trials, but the solution and the result was worth the effort. I hope it will help You Guys to open export Excel file during SAP scripting and make possible to continue coding.
Hi
I am very new to Excel vba.
I’ve been able to run a SAPgui script via excel, and get it to perform a complete transaction.
However what I would like to know is there a simple way to extract info from certain fields in SAP and extract it to an active excel sheet.
The help will be much appreciated.
Thanks in advance
Hello Clarence
Please check the SAP category of this site / other SAP articles on my site. They should be helpful.
Search engine will be helpful 🙂
Thanks for the reply.
I’ve been checking the SAP category for a possible solution and not finding it.
Perhaps you could point me in the right direction.
I’ve also been googling/watching YouTube videos. The videos I come accross appears abit too advance for a beginner.
Here’s my script
If Not IsObject(application) Then
Set SapGuiAuto = GetObject(“SAPGUI”)
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.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 application, “on”
End If
session.findById(“wnd[0]”).maximize
session.findById(“wnd[0]/tbar[0]/okcd”).text = “recn”
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/usr/subSUB_OBJSEL:SAPLRECN_GUI_CN_SEL:1000/ctxtRECN_CONTRACT_X-BUKRS”).text = “coct”
session.findById(“wnd[0]/usr/subSUB_OBJSEL:SAPLRECN_GUI_CN_SEL:1000/ctxtRECN_CONTRACT_X-RECNNR”).text = “903997354”
session.findById(“wnd[0]/usr/subSUB_OBJSEL:SAPLRECN_GUI_CN_SEL:1000/ctxtRECN_CONTRACT_X-RECNNR”).setFocus
session.findById(“wnd[0]/usr/subSUB_OBJSEL:SAPLRECN_GUI_CN_SEL:1000/ctxtRECN_CONTRACT_X-RECNNR”).caretPosition = 9
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/tbar[1]/btn[25]”).press
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_02”).select
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_02/ssubSUB_BDT_DYNPRO:SAPLREGC:1020/subSUB_PARTNER:SAPLRECA_GUI_DYNPRO_SUBSCREEN:1000/subSUB_BOTTOM:SAPLREBP_GUI_OBJREL_LIST:3000/subSUB_OBJREL:SAPLREBP_GUI_OBJREL:1000/ctxtREBP_OBJREL_S-PARTNER”).setFocus
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_02/ssubSUB_BDT_DYNPRO:SAPLREGC:1020/subSUB_PARTNER:SAPLRECA_GUI_DYNPRO_SUBSCREEN:1000/subSUB_BOTTOM:SAPLREBP_GUI_OBJREL_LIST:3000/subSUB_OBJREL:SAPLREBP_GUI_OBJREL:1000/ctxtREBP_OBJREL_S-PARTNER”).caretPosition = 0
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_04”).select
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_05”).select
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_05/ssubSUB_BDT_DYNPRO:SAPLREGC:1060/subSUB_REBD_OBJ:SAPLRECA_GUI_DYNPRO_SUBSCREEN:1002/subSUB_TOP:SAPLREBD_GUI_OBJASS_BO_LIST:2000/cntlCC_OBJ_ASSIGN_BO_TREE/shellcont/shell/shellcont[1]/shell[1]”).selectItem ” 2″,”C 2″
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_05/ssubSUB_BDT_DYNPRO:SAPLREGC:1060/subSUB_REBD_OBJ:SAPLRECA_GUI_DYNPRO_SUBSCREEN:1002/subSUB_TOP:SAPLREBD_GUI_OBJASS_BO_LIST:2000/cntlCC_OBJ_ASSIGN_BO_TREE/shellcont/shell/shellcont[1]/shell[1]”).ensureVisibleHorizontalItem ” 2″,”C 2″
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_05/ssubSUB_BDT_DYNPRO:SAPLREGC:1060/subSUB_REBD_OBJ:SAPLRECA_GUI_DYNPRO_SUBSCREEN:1002/subSUB_TOP:SAPLREBD_GUI_OBJASS_BO_LIST:2000/cntlCC_OBJ_ASSIGN_BO_TREE/shellcont/shell/shellcont[1]/shell[1]”).topNode = ” 1″
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_05/ssubSUB_BDT_DYNPRO:SAPLREGC:1060/subSUB_REBD_OBJ:SAPLRECA_GUI_DYNPRO_SUBSCREEN:1002/subSUB_TOP:SAPLREBD_GUI_OBJASS_BO_LIST:2000/cntlCC_OBJ_ASSIGN_BO_TREE/shellcont/shell/shellcont[1]/shell[1]”).doubleClickItem ” 2″,”C 2″
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_01/ssubSUB_BDT_DYNPRO:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7077/subA02P01:SAPLRERO:5003/ctxtREBDROFLDS-SWENR”).setFocus
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_01/ssubSUB_BDT_DYNPRO:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7077/subA02P01:SAPLRERO:5003/ctxtREBDROFLDS-SWENR”).caretPosition = 6
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_01/s
But where is the problem?
Hi Tomasz
Only see you message now, but manage to solve my problem
Original code line:
session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_01/ssubSUB_BDT_DYNPRO:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7077/subA02P01:SAPLRERO:5003/ctxtREBDROFLDS-SWENR”).setFocus
Did the the following changes to extract a field from SAP to a cell to the active sheet.
Eg: entering destination cell at beginning and changing “setfocus” to text right at the end.
Cells(2, 1).Value = session.findById(“wnd[0]/usr/subSUB_BDT:SAPLRECA_BDT_APPL_TOOL:1000/tabsBDT_TABSTRIP/tabpREBDT_TAB_01/ssubSUB_BDT_DYNPRO:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7077/subA02P01:SAPLRERO:5003/ctxtREBDROFLDS-SWENR”).Text
Great job! Good for You! 🙂
I looked all over for the answer to this a few years ago. Eventually I had to do my own experiments and I came up with pretty much the same thing. The one difference is I cycle through each excel instance. If multiple instances are open it’s not a sure thing the export will open in the one the script is running in. I also have a couple of places where the export opens in its own instance. When I just checked the application instance I was in, the script worked 95% of the time. So the instances issue didn’t happen often, but many people use my scripts and failing on them isn’t great. It only has to fail once for people to say your stuff never works right haha.
Yeah, I feel You 😀
Tomas, could you help me? A don’t manage to close files after macros. I get zerul and mb51 in excel, but for power query I need to close these files. Refresh all doesn’t work while files don’t close. Help not to open but to close excel files in macros.
Just simple .Close on Workbooks? 🙂
Hi Tomasz,
thank you so much for this hint. I really tried a few weeks to download a SAP Table with SE16 and make a few changes via VBA and it always didn’t work. But with the DoEvents Method it works perfect.
I only have a little other problem now. I want that the Macro starts automaticly when I open the Excel-File. So I used the “Private Sub Workbook_Open()” Method under MyWorbooks. If I then open the file directly from the Explorer (Excel is not running) it doesn’t work, the makro keep doing the loop and doesn’t open the SAP Excel File. As soon I stop the Macro, the file opens.
But if I open the file while Excel is running, everything works fine.
Do you maybe have an idea what could be the reason?
Thank you very much!!
Markus
To be honest I only used “Workbook_Open” to check how it works, nothing important, so can’t really tell You anything about its behaviour.
Maybe I (and the community from here) can help You after seeing the code 🙂
Dear Tomasz,
I am not an expert but I have no idea why my skript stops at DoEvents and nothing happens. I can wait nothing happens when I hit pause then I see that I am stuck at DoEvents. Any Idea what I could try?
Best regards,
Thomas
Sub SapConn()
Dim Appl As Object
Dim Connection As Object
Dim session As Object
Dim WshShell As Object
Dim SapGui As Object
Dim WScript As Object
‘Wenn SAP Offen dann schließen: Muss noch erstellt werden.
‘Texdatei für SAP wird generiert
Call TextdateiSchreiben
‘SAP wird gestartet
Shell “C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe”, 4
Set WshShell = CreateObject(“WScript.Shell”)
Do Until WshShell.AppActivate(“SAP Logon “)
Application.Wait Now + TimeValue(“0:00:01”)
Loop
Set WshShell = Nothing
Set SapGui = GetObject(“SAPGUI”)
Set Appl = SapGui.GetScriptingEngine
Set Connection = Appl.Openconnection(“(111) PAS SSO ArianeGroup ERP Produktion”, _
True)
Set session = Connection.Children(0)
‘recorded macro:
session.findById(“wnd[0]”).maximize
‘implementation of a count according to https://simpleexcelvba.com/how-to-save-as-and-open-excel-during-sap-scripting/
Dim appWorkbookcount As Long
appWorkbookcount = Application.Workbooks.Count
session.findById(“wnd[0]/tbar[0]/okcd”).Text = “/nCJi3”
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/usr/ctxtR_KSTAR-LOW”).Text = “”
session.findById(“wnd[0]/usr/ctxtKOAGR”).Text = “”
session.findById(“wnd[0]/usr/ctxtKOAGR”).SetFocus
session.findById(“wnd[0]/usr/ctxtKOAGR”).caretPosition = 0
session.findById(“wnd[0]/usr/btn%_CN_PSPNR_%_APP_%-VALU_PUSH”).press
session.findById(“wnd[1]/tbar[0]/btn[23]”).press
session.findById(“wnd[2]/usr/ctxtDY_PATH”).Text = ThisWorkbook.Path + “\PSP-Register” ‘Ordner in dem das PSP-Register als Textdatei liegt
session.findById(“wnd[2]/usr/ctxtDY_FILENAME”).Text = “PSP-Register.txt” ‘Name der PSP-Register Textdatei
session.findById(“wnd[2]/usr/ctxtDY_FILENAME”).caretPosition = 16
session.findById(“wnd[2]/tbar[0]/btn[0]”).press
session.findById(“wnd[1]/tbar[0]/btn[8]”).press
session.findById(“wnd[0]/usr/ctxtR_BUDAT-LOW”).Text = Sheets(“PSP_Register”).Cells(14, 5).Value
session.findById(“wnd[0]/usr/ctxtR_BUDAT-HIGH”).Text = Sheets(“PSP_Register”).Cells(15, 5).Value
session.findById(“wnd[0]/usr/ctxtP_DISVAR”).Text = “/ALL_FIELDS” ‘SAP Layout muss evtl. erst angewählt oder angepasst werden, je nach Daten die man braucht.
session.findById(“wnd[0]/usr/ctxtP_DISVAR”).SetFocus
session.findById(“wnd[0]/usr/ctxtP_DISVAR”).caretPosition = 10
session.findById(“wnd[0]/usr/btnBUT1”).press
session.findById(“wnd[1]/usr/txtKAEP_SETT-MAXSEL”).Text = “5.000.000”
session.findById(“wnd[1]/usr/txtKAEP_SETT-MAXSEL”).caretPosition = 9
session.findById(“wnd[1]/tbar[0]/btn[0]”).press
session.findById(“wnd[0]/tbar[1]/btn[8]”).press
session.findById(“wnd[0]/tbar[1]/btn[43]”).press
session.findById(“wnd[1]/tbar[0]/btn[0]”).press
session.findById(“wnd[1]/usr/ctxtDY_PATH”).Text = ThisWorkbook.Path + “\SAP-Export” ‘Ordner in dem der SAP Export abgelegt wird
session.findById(“wnd[1]/usr/ctxtDY_FILENAME”).Text = “export1.xlsx” ‘Dateiname des SAP Exports
session.findById(“wnd[1]/usr/ctxtDY_FILENAME”).caretPosition = 15
session.findById(“wnd[1]/tbar[0]/btn[11]”).press
Do
DoEvents
Loop While appWorkbookcount = Application.Workbooks.Count
Dim wb As Workbook, wbExport As Workbook
For Each wb In Application.Workbooks
Debug.Print wb.Name
If InStr(1, wb.Name, “export”) > 0 Then
Set wbExport = wb
End If
Next wb
Workbooks(“PSP-Export.xlsx”).Close
‘Closing the SAP Session
Application.Wait (Now + TimeValue(“0:00:02”))
While Connection.Children.Count > 0
Set session = Connection.Children(0)
session.findById(“wnd[0]”).Close
On Error Resume Next
session.findById(“wnd[1]/usr/btnSPOP-OPTION1”).press
On Error GoTo 0
Wend
‘Import SAP Export into this Worksheet and Clean up of DATA (Removal of Sum Cells)
Call CopySheetFromClosedWB
Call Data_CleanUp
Call SpaltenFilter
Call Verweis
End Sub
I tried this, and it works:
Credit to:
https://stackoverflow.com/questions/64009373/close-sap-export-multiple-excel-file-with-vba
Dim myCount As Long
myCount = Workbooks.Count
Do
DoEvents
Application.Wait now + TimeSerial(0, 0, 2)
DoEvents
If Workbooks.Count > myCount Then Exit Do
Loop
In general this is the same solution 🙂
Thank you for the help, this is a very popular issue.
for the code below that you made:
Dim wb As Workbook, wbExport As WorkbookRedaktionelle Leitlinien
For Each wb In Application.Workbooks
If InStr(1, wb.Name, “export”) > 0 Then
Set wbExport = wb
End If
Next wb
I am coding in english and getting an error at “WorkbookRedaktionelle Leitlinien”. What is this meant to be???
There is no such variable like “WorkbookRedaktionelle Leitlinien” 🙂
Hello Tomasz,
Thank you for providing the code. I encountered the same issue, and your solution works perfectly for me.
Regarding “WorkbookRedaktionelle Leitlinien,” I also found a reference to “modafinil-schweiz.site/” which appears to be unintended in the code. I’m reaching out to inform you that something might have altered the code on the page.
I’m sorry for that.
I’s probably spam attacks on my website, struggling with that all the time =(