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.

12 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

    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 🙂

      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

          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

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

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

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

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

Leave a Reply

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