SAP & VBA: simple Excel application with User Form

SAP Scripting - VBA

SAP GUI Scripting is an automation interface that enhances the capabilities of SAP GUI. Using Visual Basic users may automate repetitive tasks by running scripts. Developers on the other hand may build complex tools to make repetitive time-consuming tasks quick and easy.

In SAP and VBA: How to Start blog post, you can check how to start working with SAP GUI scripting. Below you can find simple example of the code to be run from Microsoft Excel.

Example

In the post I will show how to create simple Excel application with UserForm. In the UserForm user will be able to choose SAP Transaction (T-Code) and then the chosen transaction will be run in SAP.

Tutorial

  • Open Excel and run Visual Basic editor (from Developer tab).
  • Using Visual Basic editor in MS Excel add the simple User Form to your application.

  • Design your User Form. Using Toolbox, add controls: label, combo box and command button.
  • Using Properties window, change parameters of your components (for example captions).
  • Right click on your User Form and view the code. You need to write your VBA code here.
  • Add the following code to your user form.
Sub UserForm_initialize()
ComboBox1.AddItem "SU01" 'Use another transaction
ComboBox1.AddItem "SU51"
ComboBox1.AddItem "SU53"
'Add more transactions here
End Sub
Private Sub CommandButton1_Click()
Dim Transaction As String
If Not IsObject(Application_SAP) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set Application_SAP = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then Set Connection = Application_SAP.Children(0)
If Not IsObject(session) Then Set session = Connection.Children(0)
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject Application_SAP, "on"
End If
Transaction = ComboBox1.Value
Transaction = "/n" & Transaction
session.findById("wnd[0]/tbar[0]/okcd").Text = Transaction
session.findById("wnd[0]").sendVKey 0
Unload Me
End Sub

The first part, UserForm_initialize is used to add three transactions to your combo box (user will be able to select one of them he opens User Form). The second part, CommandButton1_Click will be triggered when user clicks the button. It will connect to the SAP GUI, take the transaction chosen in combo box and open it in the same SAP window.

  • As a final step, you need to create a short code to run your user form. Add a module to your project and open it (double click).

Add following code in your module. If you changed the name of user form, adjust the code.

Sub RunUserForm()
UserForm1.Show
End Sub

Now you are run to run your basic program. Go to Excel, open Macros on the Developer tab, select your macro and hit run. Remember, that you need to be logged in to SAP GUI.

The solution presented in this post is very simple, especially for experienced VBA users, but it can be the base for further SAP Scripting development.

Leave a Reply

Your email address will not be published. Required fields are marked *