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 0
I 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 wb
Unfortunately, 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.Count
Then, 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.Count
Thanks 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 wb
So 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 wb
It 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! 🙂