How to run SAP GUI script from Excel Macro

excel vba automation sap-gui
Ashique Sheikh picture Ashique Sheikh · Jul 19, 2017 · Viewed 29.3k times · Source

I am trying to create a Excel macro which executes the SAP GUI Script. I already created the script but I didn't understand how to use that in VBA macro.

This is my SAP GUI Script :

If Not IsObject(application) Then
   Set SapGuiAuto  = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.Children(0)
End If
If Not IsObject(session) Then
   Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session,     "on"
   WScript.ConnectObject application, "on"
End If

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "ZL"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/chkP_DBAGG").selected = true
session.findById("wnd[0]/usr/ctxtP_DTA").text = "DB"
session.findById("wnd[0]/usr/chkP_DBAGG").setFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[25]").press
session.findById("wnd[0]/tbar[1]/btn[26]").press
session.findById("wnd[0]/usr/chkS005").selected = true
session.findById("wnd[0]/usr/chkS017").selected = true
session.findById("wnd[0]/usr/chkS018").selected = true
session.findById("wnd[0]/usr/chkS020").selected = true
session.findById("wnd[0]/usr/chkS025").selected = true
session.findById("wnd[0]/usr/chkS030").selected = true
session.findById("wnd[0]/usr/chkS031").selected = true
session.findById("wnd[0]/usr/chkS055").selected = true
session.findById("wnd[0]/usr/chkS057").selected = true
session.findById("wnd[0]/usr/chkS057").setFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/ctxtC025-LOW").setFocus
session.findById("wnd[0]/usr/ctxtC025-LOW").caretPosition = 0
session.findById("wnd[0]").sendVKey 4
session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20170717,20170717"
session.findById("wnd[0]/usr/ctxtC025-HIGH").setFocus
session.findById("wnd[0]/usr/ctxtC025-HIGH").caretPosition = 0
session.findById("wnd[0]").sendVKey 4
session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").focusDate = "20170724"
session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20170724,20170724"
session.findById("wnd[0]/usr/txtL_MX").text = "9999999"
session.findById("wnd[0]/usr/txtL_MX").setFocus
session.findById("wnd[0]/usr/txtL_MX").caretPosition = 11
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").select
session.findById("wnd[1]/usr/ctxtDY_PATH").setFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0
session.findById("wnd[1]").sendVKey 4
session.findById("wnd[2]/usr/ctxtDY_PATH").setFocus
session.findById("wnd[2]/usr/ctxtDY_PATH").caretPosition = 0
session.findById("wnd[2]").sendVKey 4
session.findById("wnd[3]/usr/ctxtDY_PATH").setFocus
session.findById("wnd[3]/usr/ctxtDY_PATH").caretPosition = 0
session.findById("wnd[3]").sendVKey 4
session.findById("wnd[4]/usr/ctxtDY_PATH").text = "G:\PROFILES\AP\Desktop"
session.findById("wnd[4]/usr/ctxtDY_FILENAME").text = "report.xlsx"
session.findById("wnd[4]/usr/ctxtDY_FILENAME").caretPosition = 11
session.findById("wnd[4]/tbar[0]/btn[11]").press
session.findById("wnd[3]/tbar[0]/btn[11]").press
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[11]").press

I have few doubts regarding this:

  1. How can I add this in Excel macro.
  2. Do I need to open the SAP GUI application manually before running it?
  3. Is there any add-on code to open the SAP GUI application from Excel macro?

I only need button click to perform the process. I didn't pass any value dynamically.

Answer

Steve W picture Steve W · Dec 2, 2018

Open and log in to SAP manually.

Open Excel, make sure the developer tab is visible. If not then select;

File/Options/Customize Ribbon. Make sure 'Developer' is ticked.

Select the Developer tab in Excel then choose 'Insert'. From the 'Active X' set of controls (Not the 'FORM' controls') select the Command Button control and draw it on your worksheet. Right-click the button and select 'Properties', look for 'Caption' and either delete it or change it to something more appropriate. After that, right-click the command button again and select 'View Code'. Paste the following code into the vba editor.

On the VBA editor menu select; Debug/Compile VBA Project. Hopefully there will be no errors and you can now close the VBA editor.

Now, click your button and the code should run your SAP transaction. If the button won't click, make sure 'Design Mode' isn't selected in the Developer tab.

Don't forget to save your spreadsheet :)

Private Sub CommandButton1_Click()

On Error GoTo Err_NoSAP

If Not IsObject(SAPGuiApp) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SAPGuiApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = SAPGuiApp.Children(0)
End If
If Not IsObject(SAP_session) Then
   Set SAP_session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject SAP_session, "on"
   WScript.ConnectObject SAPGuiApp, "on"
End If

If (Connection.Children.Count > 1) Then GoTo Err_TooManySAP

Set aw = SAP_session.ActiveWindow()
aw.findById("wnd[0]").Maximize

On Error GoTo Err_Description
SAP_session.findById("wnd[0]").Maximize
SAP_session.findById("wnd[0]/tbar[0]/okcd").Text = "ZL"
SAP_session.findById("wnd[0]").sendVKey 0
SAP_session.findById("wnd[0]/usr/chkP_DBAGG").Selected = True
SAP_session.findById("wnd[0]/usr/ctxtP_DTA").Text = "DB"
SAP_session.findById("wnd[0]/usr/chkP_DBAGG").SetFocus
SAP_session.findById("wnd[0]/tbar[1]/btn[8]").press
SAP_session.findById("wnd[0]/tbar[1]/btn[25]").press
SAP_session.findById("wnd[0]/tbar[1]/btn[26]").press
SAP_session.findById("wnd[0]/usr/chkS005").Selected = True
SAP_session.findById("wnd[0]/usr/chkS017").Selected = True
SAP_session.findById("wnd[0]/usr/chkS018").Selected = True
SAP_session.findById("wnd[0]/usr/chkS020").Selected = True
SAP_session.findById("wnd[0]/usr/chkS025").Selected = True
SAP_session.findById("wnd[0]/usr/chkS030").Selected = True
SAP_session.findById("wnd[0]/usr/chkS031").Selected = True
SAP_session.findById("wnd[0]/usr/chkS055").Selected = True
SAP_session.findById("wnd[0]/usr/chkS057").Selected = True
SAP_session.findById("wnd[0]/usr/chkS057").SetFocus
SAP_session.findById("wnd[0]/tbar[1]/btn[8]").press
SAP_session.findById("wnd[0]/usr/ctxtC025-LOW").SetFocus
SAP_session.findById("wnd[0]/usr/ctxtC025-LOW").caretPosition = 0
SAP_session.findById("wnd[0]").sendVKey 4
SAP_session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20170717,20170717"
SAP_session.findById("wnd[0]/usr/ctxtC025-HIGH").SetFocus
SAP_session.findById("wnd[0]/usr/ctxtC025-HIGH").caretPosition = 0
SAP_session.findById("wnd[0]").sendVKey 4
SAP_session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").focusDate = "20170724"
SAP_session.findById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20170724,20170724"
SAP_session.findById("wnd[0]/usr/txtL_MX").Text = "9999999"
SAP_session.findById("wnd[0]/usr/txtL_MX").SetFocus
SAP_session.findById("wnd[0]/usr/txtL_MX").caretPosition = 11
SAP_session.findById("wnd[0]/tbar[1]/btn[8]").press
SAP_session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
SAP_session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
SAP_session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0
SAP_session.findById("wnd[1]").sendVKey 4
SAP_session.findById("wnd[2]/usr/ctxtDY_PATH").SetFocus
SAP_session.findById("wnd[2]/usr/ctxtDY_PATH").caretPosition = 0
SAP_session.findById("wnd[2]").sendVKey 4
SAP_session.findById("wnd[3]/usr/ctxtDY_PATH").SetFocus
SAP_session.findById("wnd[3]/usr/ctxtDY_PATH").caretPosition = 0
SAP_session.findById("wnd[3]").sendVKey 4
SAP_session.findById("wnd[4]/usr/ctxtDY_PATH").Text = "G:\PROFILES\AP\Desktop"
SAP_session.findById("wnd[4]/usr/ctxtDY_FILENAME").Text = "report.xlsx"
SAP_session.findById("wnd[4]/usr/ctxtDY_FILENAME").caretPosition = 11
SAP_session.findById("wnd[4]/tbar[0]/btn[11]").press
SAP_session.findById("wnd[3]/tbar[0]/btn[11]").press
SAP_session.findById("wnd[2]/tbar[0]/btn[0]").press
SAP_session.findById("wnd[1]/tbar[0]/btn[11]").press

Exit Sub

Err_Description:
    MsgBox ("The program has generated an error;" & Chr(13) & _
    "the reason for this error is unknown."), VbInformation, _
    "For Information..."
        Exit Sub

Err_NoSAP:
MsgBox ("You don't have SAP open or " & Chr(13) & _
"scripting has been disabled."), VbInformation, _
"For Information..."
        Exit Sub

Err_TooManySAP:
MsgBox ("You must only have one SAP session open. " & Chr(13) & _
        "Please close all other open SAP sessions."), VbInformation, _
        "For Information..."
         Exit Sub

End Sub

Related questions

automatically execute an Excel macro on a cell change

How can I automatically execute an Excel macro each time a value in a particular cell changes? Right now, my working code is: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("H5")) Is Nothing Then …

Read More
Excel VBA Open workbook, perform actions, save as, close

This question has been edited due to lengthy comments and updates from proposed answers. As requested here is module 13; Sub SaveInFormat() Application.DisplayAlerts = False Workbooks.Application.ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\jammil\Desktop\AutoFinance\ProjectControl\Data\" & Format(…

Read More
VBA interaction with internet explorer

The macro I am building takes names from an Excel spreadsheet, opens Internet Explorer, and searches the online directory. After searching the directory, it pulls up a Java form with the manager's name in it. I am able to manually …

Read More