The world’s leading publication for data science, AI, and ML professionals.

Automatically run and export SAP transactions out of Excel using VBA

Do you frequently have to execute a specific SAP transaction and export the result as an Excel file? If so, and if you want to automate…

Automate the boring stuff with SAP and Excel GUI.

Shout-Out to boredom! (Blue Moon, by Matthias Böhler, with friendly permission from my friend Matthias)
Shout-Out to boredom! (Blue Moon, by Matthias Böhler, with friendly permission from my friend Matthias)

Motivation

Do you frequently have to execute SAP transactions with specific filter parameters and export the result as an Excel file? If so, and if you want to automate these steps using Excel VBA and SAP scripts, this is your story. We will automatically run SAP transactions from an Excel file with defined filters/settings, export the results out of SAP, and save the result as an Excel file without further manual input. This is very helpful when we have to run specific SAP transactions frequently, always using the same filter/parameter settings. Let’s start automating this boring, repetitive manual work and build an Excel-SAP GUI.

Solution

You can record your inputs using the "Script Recording and playback" functionality in SAP. This is very similar to, e.g., Excel’s macro recorder. To do this in SAP, you must press ALT+F12 and then click "Script Recording and playback":

Although in German, the sequence order is the same, no matter what SAP language you are using.
Although in German, the sequence order is the same, no matter what SAP language you are using.
Click on the red button to record your SAP actions.
Click on the red button to record your SAP actions.

After you click the red "record" button, everything you do in SAP will be recorded until you stop the recording via the "Stop" button.

In our example, we will run the transaction "IW29", set some filters, and execute the transaction to see the results. We will also export the results out of SAP as an Excel file. After that, we will stop the recording and look at the SAP script file that has just been recorded. In our example, it looks like this:

We simply copy and paste this script into our Excel file’s VBA module. Therefore, we open our Excel Macro file (e.g. .xlsm) and add the below code (Sub Routine) into our module:

Sub SapExport()
If Not IsObject(SAPapplication) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set SAPapplication = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(SAPconnection) Then
Set SAPconnection = SAPapplication.Children(0)
End If
If Not IsObject(SAPsession) Then
Set SAPsession = SAPconnection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject SAPsession, "on"
WScript.ConnectObject Application, "on"
End If

This piece of VBA code will set the connection between our Excel file and SAP. Each time we run this code, SAP will show us this message, which needs to be confirmed first before the script can go on:

SAP message popping up "A script is trying to access the SAP GUI"
SAP message popping up "A script is trying to access the SAP GUI"

In case you want to get rid of this "A script is trying to attach to SAP GUI, please confirm via Okay button" message, you have to customize your local layout in SAP as below:

Press (ALT+F12) in SAP to "Customize local layout." Then click on "Options," click "Accessibility&Scripting," and finally choose "Scripting." Now untick the box ‘Notify when a script attaches to SAP GUI’ and ‘Notify when a script opens a connection.’ So, in the end, the SAP scripting setting should look like this:

Paste the recorded session tasks (transaction filters) into your Excel VBA module. The only modification you have to do is to replace session with SAPsession, so finally, it looks something like this in your Excel module:

SAPsession.findById("wnd[0]").maximize
SAPsession.findById("wnd[0]/tbar[0]/okcd").Text = "iw29"
SAPsession.findById("wnd[0]").sendVKey 0
SAPsession.findById("wnd[0]/usr/chkDY_MAB").Selected = False
SAPsession.findById("wnd[0]/usr/ctxtQMART-LOW").Text = "z2"
SAPsession.findById("wnd[0]/usr/ctxtDATUV").Text = "10012021"
SAPsession.findById("wnd[0]/usr/ctxtDATUB").Text = "26012021"
SAPsession.findById("wnd[0]/usr/ctxtDATUB").SetFocus
SAPsession.findById("wnd[0]/usr/ctxtDATUB").caretPosition = 8
SAPsession.findById("wnd[0]").sendVKey 0
SAPsession.findById("wnd[1]/tbar[0]/btn[0]").press
SAPsession.findById("wnd[0]/tbar[1]/btn[16]").press
SAPsession.findById("wnd[1]/tbar[0]/btn[0]").press
SAPsession.findById("wnd[1]/tbar[0]/btn[0]").press
SAPsession.findById("wnd[1]/tbar[0]/btn[0]").press

We have run the SAP transactions with defined filters and exported the results as an Excel file. Finally, we also want to save this file to a defined path. Below, you will find an easy solution for this, which is already sufficient enough for our task:

'Disable the Application Alert before saving the file
Application.DisplayAlerts = False
Windows("FileName").Activate
ActiveWorkbook.SaveAs FileName:="C:FileName.xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
'Enabling the Application Alerts after saving the file
Application.DisplayAlerts = True
'to "clean up" SAP we return back to SAP's Easy Access:
SAPsession.findById("wnd[1]/tbar[0]/btn[0]").press
SAPsession.findById("wnd[0]/tbar[0]/btn[3]").press
SAPsession.findById("wnd[0]/tbar[0]/btn[3]").press
End sub()

If you added all this VBA code into one Subroutine in Excel, you’re done. In the future, you can just start calling your SAP transaction with defined filters out of Excel.

Note: for this VBA code to work correctly, you must have one instance of SAP open (in other words, you must already be logged in). Please ensure only one SAP mode is active, and this one instance must be in start mode:

You must have SAP Easy Access opened before starting the VBA
You must have SAP Easy Access opened before starting the VBA

Big up yourself, you are now able to export result lists out of SAP in an automated way using Excel! You can find the complete Excel Macro on my Github.

If you have more BI related questions about SAP, you can get them answered on my website: DAR-Analytics

Many thanks for reading! I hope this article is helpful for you. Feel free to connect with me on LinkedIn, Twitter or Workrooms.

Join Medium with my referral link – Jesko Rehberg


Written By

Topics:

Related Articles