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.
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").maximize Session.findById("wnd/usr/txtRSYST-MANDT").Text = Client Session.findById("wnd/usr/txtRSYST-BNAME").Text = UserName Session.findById("wnd/usr/pwdRSYST-BCODE").Text = Password Session.findById("wnd").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").sendVKey 0 While Connection.Children.Count > 0 Set MySession = Connection.Children(0) MySession.findById("wnd").Close On Error Resume Next MySession.findById("wnd/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.