Alright, so this is going to be a preview of some premium content, but for a limited time, its available to EVERYONE. So get it while you can! This won’t be up forever!


You can control SAP from Excel by using the below script in any Excel Workbook.

“GASP!, MY WORD!, MY GOODNESS” in my best southern-belle voice, clutching my pearl necklace that’s been handed down from generation to generation.

Oh yes, YOU TOO can control SAP from Microsoft Excel, and by the good grace of my goodness of good, I am going to show you how….


This tutorial assumes you have basic knowledge of using Excels VBA tools, so I am skipping over the basics on setting up a module and accessing the editor.

This script allows you to login to SAP and control the session all the way through closing SAP once you’re done. The code included shows you how to open/login to SAP, and close SAP once you’re done. Everything you do inbetween is up to you.

This script is used by my team about 50 times per day on all sorts of different SAP transactions, and it works flawlessly.


First, a Setting you Can’t Ignore

There’s one setting you need to make sure is configured properly in your SAP System in order for this to work properly. After you login to SAP, there’s a drop down menu and a section for ‘options’.


Yours may look different than mine, but rest assured – it should be there, you just need to search for it.


Under ‘Accessibility & Scripting’ you will want to check off ‘Enable Scripting’ if it’s not already checked off, and un-check the two below it. ‘Enable Scripting’ is self explanatory, but the other two are just notification settings which will hang up the script if you leave them checked. So make sure yours looks just like mine above, and you will be good to go.


The Code

To make things a little easier, consider setting up a worksheet in your workbook that has your SAP System’s login credentials and filepath. It will be easier later on if something changes. Instead of changing the code, you just change the details in the worksheet.

Mine looks like this:

UserName and Password are obvious.

Environment is the name of the instance of SAP you are using (Often looks like: “(29) P1P – Production Environment”).

Client is usually a 3 digit code (“010”)

Filepath is simply the location where your SAP is on your machine (For mine, it’s: “C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe”)

Insert the below code in a fresh module.

Sub Control_SAP_From_Excel() 
'Written by JamesCanCode 
'www.jamescancode.com 

'Declare Login Variables
 Dim UserName As String
 Dim Password As String
 Dim Environment As String
 Dim Client As String
 Dim Filepath As String
 
'Set Login Variables
'To make things easy, setup a sheet in your workbook 
'to easily store and change the below parameters
 UserName = Sheets("Login").Range("B2")
 Password = Sheets("Login").Range("B3")
 Environment = Sheets("Login").Range("B4")
 Client = Sheets("Login").Range("B5")
 Filepath = Sheets("Login").Range("B6")
 
 'Login to SAP
 Dim SapGui
 Dim Applic
 Dim Connection
 Dim Session
 Dim WSHShell
 Shell Filepath, vbNormalFocus
 Set WSHShell = CreateObject("WScript.Shell")
 Do Until WSHShell.AppActivate("SAP Logon ")
 Application.Wait Now + TimeValue("0:00:01")
 Loop
 Set WSHShell = Nothing
 Set SapGui = GetObject("SAPGUI")
 Set Applic = SapGui.GetScriptingEngine
 Set Connection = Applic.OpenConnection(Environment, True)
 Set Session = Connection.Children(0)
 Set wshell = CreateObject("WScript.Shell")
 Session.findById("wnd[0]").maximize
 Session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = Client
 Session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = UserName
 Session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = Password
 Session.findById("wnd[0]").sendVKey 0

'Here is where you would put the code to run 
'the transaction, export it, whatever you'd like. 

'Closes SAP
 Session.SendCommand ("/nMAINMENU")
 Session.findById("wnd[0]").sendVKey 0
 While Connection.Children.Count > 0
 Set MySession = Connection.Children(0)
 MySession.findById("wnd[0]").Close
 On Error Resume Next
 MySession.findById("wnd[1]/usr/btnSPOP-OPTION1").press
 On Error GoTo 0
 Wend
 End Sub

That’s it. The above code is all you need to log in and close an instance of SAP via Microsoft Excel. Excel can control the entire session.

Next, be on the lookout on how to generate the VB Code to run your SAP Transactions, Export the Info Into Excel, and Format Everything to be useable data.

Advertisements