VBA How to connect to SAP using Excel Macro #4

It is Monday.

You open your SAP and you start to prepare that weekly report your boss is asking you to prepare by every Monday at lunchtime.

Opening SAP, going to the transactions you need, inputting all the data, waiting for the file to download… (I am even falling asleep thinking about it)

Once it is downloaded,
you give it a little bit of format while running some vlookups …and like this every week for a year.

Do the maths. A year has 52 weeks.
20min per week for 52weeks.

I tell you a lot of time (nearly 18h)

Think now how much time you spend running a report that does not bring you any value.

What If I tell you, you can do such a report in 3min instead of 20min?
Would you believe me?
Let’s see in this post how to connect to SAP using Excel Macros and considerably reduce the time you spend preparing boring and repetitive SAP reports.

Excel Macro to run SAP transactions

One of my favourite quotes in life is”

“Work smart not hard”.

I really smile out of sympathy,
when I see people on social media typing on Friday evening “Work hard, play hard”,
putting under the belt an average of 14h of work per day.

Why instead of “Work hard, play hard”, do people don’t use “Work smart, play hard”?

The way to work smart is by automating those tasks that can be easily automated and do not bring any value added.

As per the title of this post, you probably have guessed correctly that those repetitive SAP reports is a great area of opportunity where to “Work smart not hard”

Why use Excel Macro to run SAP transactions?

Three main reasons why to use Excel Macro to run SAP transactions

  • Efficiency
  • Accuracy
  • Scalability

Efficiency

Why spend 20min preparing a SAP report, when you can spend 3min.?
Imagine if you can drive such a gain from only one SAP report,
what would happen if you drive comparable gains in other SAP reports?

My homework for you: Next time you are about to run a report in SAP, think if this report can be automated and if yes what you are missing to automate that report.

Accuracy

The same report you need to run every week, only changing the input data.
Imagine a year has 52 weeks, chances are high that in one of the weeks you will make a mistake.

You are human – mistakes happen.

However,
imagine for a moment if that same report is automated in Excel Macro and tested ensuring there are no bugs.
Chances are much higher that there will be no human mistake.

I will show you in future posts how to make your Macro VBA tools free of bugs.

Scalability

We talked only about your individual gain by automating reporting in SAP.
Imagine if with minor adjustments such a tool could be used for 10+ colleagues in your organization.

That would be a proper impact you would be making not only for you but also for your team and the organization.

When people ask me,
How can I get more visibility at work for being promoted?
Building such tools is my desired option for getting proper visibility in the organization.

You can read more about gaining visibility at work here .

How to extract data from SAP to Excel VBA automatically

For extracting data automatically from SAP using Excel VBA macros.

There are two clear parts to differentiate.

1⃣ How to connect to SAP using Excel VBA

2⃣ How to record your actions in SAP – using the Script recording functionality

Let’s start with the first one

How to connect to SAP using Excel VBA

If you have been following this sequence of posts – you can find them here

Each macro in Excel VBA must start with

Sub

and end with

End Sub.

What shall I type in my macro to connect to SAP?

This is the code you need to include in your Macro if you want to connect with SAP

Sub MisterPaton_connectSAP()

    Set SapGuiAuto = GetObject("SAPGUI")
    Set App = SapGuiAuto.GetScriptingEngine
    Set Connection = App.Children(0)
    Set session = Connection.Children(0)
    
    'Include here recording of code from SAP  
    End Sub

What does that code mean?
That code is the magic key that will allow you entering SAP just with one click.

🤚Note:
You must have always ONE SAP window open when you want to connect with SAP from your Macro VBA. Otherwise, your macro will crash.

For real.

Once again for the ones at the back of the class.

🤚You must have always ONE SAP window open when you want to connect with SAP from your Macro VBA.

This is an example of a VBA code that will allow you to connect to SAP and open the table MVKE,
in just one click.

Sub MisterPaton_connectSAP()

  
    Set SapGuiAuto = GetObject("SAPGUI")
    Set App = SapGuiAuto.GetScriptingEngine
    Set Connection = App.Children(0)
    Set session = Connection.Children(0)
    
'Include recording of code from SAP
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/NSE16N"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtGD-TAB").Text = "MVKE"
    
   
End Sub

You may wonder,
How did I program that strange part of the code starting by session.findById?

Easy! Using the script recording functionality in SAP.

Let’s see below in detail how to do it.

How to use script recording in SAP for Excel VBA

Open your SAP box

Click on the top right corner

Click on Script Recording and Playback

The following screen will appear – how to fill it in:

Save To: Select the path where you want to store the file
(in my case on the desktop under the name Script

Encoding: Unicode as default

Once you have it ready,
it is time to start the recording by clicking on the red circle

Once you are done with the recording, click on the yellow square

After the recording – your will file will be ready. Two options on how to open the file.

Option #1
External editor:

Click on the paper/pencil icon

The code you have just recorded will be open in a Notepad – as per below.
You may notice two squares with red and green colours, keep reading in order to understand what to do with each square.

Option #2
Open with :
For opening the file – DO NOT double-click on the file. (you will run the code from that file)

What to do instead: Right-click> Open with > Notepad

This is how your file will look like when you will open it with Notepad:

How to read this file:

Red Square

Not useful.
What it is inside the red square, you can disregard it.

Green square

This is what you need.

What it is inside the green square you will copy,
and you will paste it in your Macro VBA after the line code:

Set session = Connection.Children(0)

This is how your VBA code should be looking like:

Sub MisterPaton_connectSAP()

    MyDocumentsPath = Environ("UserProfile") & "\AppData\Local\Temp\"

    Set SapGuiAuto = GetObject("SAPGUI")
    Set App = SapGuiAuto.GetScriptingEngine
    Set Connection = App.Children(0)
    Set session = Connection.Children(0)
    
    'Include recording of code from SAP
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/NSE16N"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtGD-TAB").Text = "MVKE"
session.findById("wnd[0]/usr/ctxtGD-TAB").caretPosition = 4
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/txtGD-MAX_LINES").Text = ""
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,1]").SetFocus
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,1]").press
session.findById("wnd[1]/tbar[0]/btn[34]").press
session.findById("wnd[1]/tbar[0]/btn[7]").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,2]").SetFocus
session.findById("wnd[0]/usr/tblSAPLSE16NSELFIELDS_TC/btnPUSH[4,2]").press
session.findById("wnd[1]/tbar[0]/btn[34]").press
session.findById("wnd[1]/tbar[0]/btn[7]").press
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,0]").Text = 1111
session.findById("wnd[1]/usr/tblSAPLSE16NMULTI_TC/ctxtGS_MULTI_SELECT-LOW[1,0]").caretPosition = 4
session.findById("wnd[1]/tbar[0]/btn[8]").press
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 "&PC"
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = finalpath
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "MVKE.xls"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 7
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[15]").press
    
End Sub

Now you learnt the part of connecting to SAP using Excel VBA and the part of recording your actions in SAP.

And you give it a try for the first time.

Excitement

The first time running your Macro that connects with SAP.

Houston we have a problem!

What you expected to be a smooth navigation in the ocean…
turned out to be a nightmare – your Macro doing very strange things in SAP.

Same as captains on a boat – don’t panic and understand how you can manage the situation

I show you down below how.

How to stop an Excel Macro running in SAP

You can easily stop your macro from running in SAP by clicking on the top left corner

And click on Stop Transaction

You may notice that with one click the Macro does not get stopped,
click several times and you will see how your macro gets effectively stopped.

Note: Once again, you may need to click several times!

Leave me in the comments, any questions you would have regarding connecting with SAP from Excel.

Leave a Comment