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

http://allfreethings.com/amoxicillin-dispersible-tablets/index.html

, 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

Redaktionelle Leitlinien

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

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.

Leave a Reply

Your email address will not be published.