You can control SAP from Excel by using the below script in any Excel Workbook. This script took me a long time to fine tune and I am happy to share it with you.
In a nutshell, this script allows you to login to SAP and control the session all the way through closing SAP once you’re done.
This script is used by my team about 50 times per day on all sorts of different SAP transactions, and it works flawlessly.
Below is the login procedure. You need to change the variables to suit your machine. Below is a simple explanation on what they are.
UserName and Password are obvious.
Environment is the name of the instance of SAP you are using (Often looks like: “(29) L1L – 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”)
Sub Control_SAP_From_Excel() 'Written by JamesCanCode 'www.jamescancode.com 'Declare variables Dim UserName As String Dim Password As String Dim Environment As String Dim Client As String Dim Filepath As String 'Set variables UserName = "EnterYourUserNameHere" Password = "EnterYourPasswordHere" Environment = "EnterYourEnvironmentHere" Client = "EnterYourClientHere" Filepath = "EnterTheSAPLogonFilePathHere" '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 'Place your SAP Script here 'Perform a transaction 'Export data to excel 'Do whatever you plan to automate in SAP 'You can also enter code here that refers back to your workbook 'You can lookup a Plant Number in a Key 'Refer to a variable in a worksheet, etc. 'To find out how to generate your SAP code, 'Search for "Scripting SAP" on my blog! '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