How Do I Control SAP from Excel?

Good Question!

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[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

'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[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
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s