How to Save As and Open Excel during SAP scripting?

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 
modafinil-schweiz.site/
 , 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 
http://antolaphoto.com/prednisolone/index.html
 ,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
Redaktionelle Leitlinien

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.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

6 thoughts on “How to Save As and Open Excel during SAP scripting?”

  1. 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

    Reply

    1. 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 🙂

      Reply

      1. 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

        Reply

          1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *