Send data from Excel to SAP GUI with Excel VBA

Introduction: Automating Vendor Creation in SAP Using Excel VBA

In the dynamic world of enterprise resource planning, efficiency and accuracy are paramount. This article delves into a cutting-edge approach to streamline one of the most crucial processes in SAP: vendor creation. We're going to explore the seamless integration of Excel VBA (Visual Basic for Applications) with SAP GUI (Graphical User Interface), a method that transforms the traditional, often tedious task of vendor data entry into a swift and error-free process.

The core of this article revolves around the automation capabilities of Excel VBA, a powerful scripting language embedded in Microsoft Excel. By leveraging this tool, businesses can transfer data directly from Excel spreadsheets to the SAP system, bypassing manual entry and significantly reducing the potential for human error. This process not only enhances data accuracy but also saves considerable time and resources.

Whether you are an SAP professional, a business analyst, or simply someone interested in optimizing business processes, this article offers valuable insights into how Excel VBA can be used to automate different processes in SAP. Get ready to transform your approach to data entry in SAP with the power of Excel VBA.

In this example, we will be inputting the following vendor list into SAP Tcode XK01

VendorCreation

Recording our Vendor Creation Process script in SAP

Recording our script is a simple process, thanks to the SAP Script Recording and Playback tool that's built into the system. I won't go into much detail here, but you can find more information in my previous article. In that article, I cover the setup of your SAP GUI environment in depth and demonstrate how to use the built-in recorder for capturing our processes. Additionally, for a more hands-on guide, you can watch my YouTube video where I walk through the recording process step-by-step.

Using recorded script as a base to build your script in VBA Editor

After recording our script, it serves as the foundation for creating our tailored script. Begin by launching the VBA editor in Excel. If you run into any syntax errors, ensure that the SAP GUI Scripting API is enabled in your Excel. For a detailed guide on activating it, refer to my previous article. In the VBA editor, it's crucial to declare the variables from the recorded script (SapGuiAuto, Application, Connection, Session) as Public. Failing to do so could lead to an "Invalid use of property" error.

Public SapGuiAuto, Application, Connection, Session

Sub VendorCreation()
   "Paste your SAP script here"
End Sub

Since the recorded script outlines the steps for creating a single vendor, we must wrap it in a loop to execute it multiple times, corresponding to the number of vendors on our list. To determine how many iterations the script should run, we need to identify the last row of the list. There are several methods to find the last row, but I'll use End(xlUp).Row for this purpose. Our loop will run from i = 2 up to the last row of the list, skipping i = 1 as it represents our column header. Below is the code that follows this logic.

Public SapGuiAuto, Application, Connection, Session

Sub VendorCreation()

Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim BPnumber As String
Dim ref As String

Set ws = ThisWorkbook.Sheets("companies")
lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
   "Paste your SAP script here"
Next i

Inside the loop, each iteration will process one row at a time. For every iteration, I will allocate the value of each column cell to a specific variable, utilizing the cells.value property before running our SAP script.

Public SapGuiAuto, Application, Connection, Session

Sub VendorCreation()

Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim BPnumber As String
Dim ref As String

Set ws = ThisWorkbook.Sheets("companies")
lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
   company = ws.Cells(i, 1).Value
   search1 = ws.Cells(i, 2).Value
   search2 = ws.Cells(i, 3).Value
   street = ws.Cells(i, 4).Value
   vip = ws.Cells(i, 5).Value
   postal = ws.Cells(i, 6).Value
   city = ws.Cells(i, 7).Value
   country = ws.Cells(i, 8).Value
   language = ws.Cells(i, 9).Value

   "Paste your SAP script here"

Next i

After assigning all the variables, the next step is to replace the hard-coded strings in the script with these dynamically assigned variables. These variables will vary based on the row being processed. Completing this step readies our script for vendor creation in SAP.

Sending information from SAP GUI to Excel

Additionally, I plan to capture certain information displayed in the SAP GUI. To identify any specific field, the recorder tool is useful. Begin recording, then double-click on the element containing the desired information. The script will record the element's ID, which you can use to set up a watch in the VBA editor. This allows you to determine which property holds the needed value.

Status bar text

To access the text on the status bar, use Session.findById("wnd[0]/sbar").Text. Once you retrieve the value, you can easily transfer it to the designated column using Cells.Value property.

For i = 2 To lastrow
   company = ws.Cells(i, 1).Value
   search1 = ws.Cells(i, 2).Value
   search2 = ws.Cells(i, 3).Value
   street = ws.Cells(i, 4).Value
   vip = ws.Cells(i, 5).Value
   postal = ws.Cells(i, 6).Value
   city = ws.Cells(i, 7).Value
   country = ws.Cells(i, 8).Value
   language = ws.Cells(i, 9).Value

   "Paste your SAP script here"
   
   ref = Session.findById("wnd[0]/sbar").Text
   ws.Cells(i, 11).Value = ref
   BPnumber = Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/subSCREEN_1000_HEADER_AREA:SAPLBUPA_DIALOG_JOEL:1510/ctxtBUS_JOEL_MAIN-CHANGE_NUMBER").displayedtext
   ws.Cells(i, 10).Value = BPnumber

Next i

Below is the completed script for the vendor creation process:

Public SapGuiAuto, Application, Connection, Session

Sub vendorCreation()

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

Dim company As String
Dim search1 As String
Dim search2 As String
Dim street As String
Dim vip As String
Dim postal As String
Dim city As String
Dim country As String
Dim language As String

Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim BPnumber As String
Dim ref As String

Set ws = ThisWorkbook.Sheets("companies")
lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow

company = ws.Cells(i, 1).Value
search1 = ws.Cells(i, 2).Value
search2 = ws.Cells(i, 3).Value
street = ws.Cells(i, 4).Value
vip = ws.Cells(i, 5).Value
postal = ws.Cells(i, 6).Value
city = ws.Cells(i, 7).Value
country = ws.Cells(i, 8).Value
language = ws.Cells(i, 9).Value

Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nxk01"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
Session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA02P01:SAPLBUD0:1130/cmbBUS000FLDS-TITLE_MEDI").Key = "0003"
Session.findById("wnd[0]").resizeWorkingPane 133, 41, False
If vip = "Y" Then
    Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA04P01:SAPLFS_BP_BDT_FS_ATTRIBUTES:0130/chkGS_BP001-VIP").Selected = True
End If
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA02P02:SAPLBUD0:1200/txtBUT000-NAME_ORG1").Text = company
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA03P01:SAPLBUD0:1110/txtBUS000FLDS-BU_SORT1_TXT").Text = search1
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA03P01:SAPLBUD0:1110/txtBUS000FLDS-BU_SORT2_TXT").Text = search2
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA05P01:SAPLBUA0:0400/subADDRESS:SAPLSZA1:0300/subCOUNTRY_SCREEN:SAPLSZA1:0301/txtADDR1_DATA-STREET").Text = street
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA05P01:SAPLBUA0:0400/subADDRESS:SAPLSZA1:0300/subCOUNTRY_SCREEN:SAPLSZA1:0301/txtADDR1_DATA-POST_CODE1").Text = postal
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA05P01:SAPLBUA0:0400/subADDRESS:SAPLSZA1:0300/subCOUNTRY_SCREEN:SAPLSZA1:0301/txtADDR1_DATA-CITY1").Text = city
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA05P01:SAPLBUA0:0400/subADDRESS:SAPLSZA1:0300/subCOUNTRY_SCREEN:SAPLSZA1:0301/ctxtADDR1_DATA-COUNTRY").Text = country
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA05P01:SAPLBUA0:0400/subADDRESS:SAPLSZA1:0300/subCOUNTRY_SCREEN:SAPLSZA1:0301/cmbADDR1_DATA-LANGU").Key = language
Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/ssubSCREEN_1000_WORKAREA_AREA:SAPLBUPA_DIALOG_JOEL:1100/ssubSCREEN_1100_MAIN_AREA:SAPLBUPA_DIALOG_JOEL:1101/tabsGS_SCREEN_1100_TABSTRIP/tabpSCREEN_1100_TAB_01/ssubSCREEN_1100_TABSTRIP_AREA:SAPLBUSS:0028/ssubGENSUB:SAPLBUSS:7016/subA05P01:SAPLBUA0:0400/subADDRESS:SAPLSZA1:0300/subCOUNTRY_SCREEN:SAPLSZA1:0301/cmbADDR1_DATA-LANGU").SetFocus
Session.findById("wnd[0]/tbar[0]/btn[11]").press

ref = Session.findById("wnd[0]/sbar").Text
ws.Cells(i, 11).Value = ref
BPnumber = Session.findById("wnd[0]/usr/subSCREEN_3000_RESIZING_AREA:SAPLBUS_LOCATOR:2036/subSCREEN_1010_RIGHT_AREA:SAPLBUPA_DIALOG_JOEL:1000/subSCREEN_1000_HEADER_AREA:SAPLBUPA_DIALOG_JOEL:1510/ctxtBUS_JOEL_MAIN-CHANGE_NUMBER").displayedtext
ws.Cells(i, 10).Value = BPnumber

Next i

End Sub

And that's it! Our script is now complete. Simply click the play button or press "F5" to start the process. Watch as SAP begins to automatically create vendors using the list you've provided. For a visual demonstration, you can watch this process in action in my YouTube video linked above.

YouTube - Automate Data Entry in SAP with Excel VBA

If you are eager to unlock the power of SAP GUI Scripting, I've got you covered! I've written a comprehensive eBook that covers everything you need to know to get started.

With this eBook, you'll gain a complete understanding of the SAP GUI object model and get access to practical code snippets that will help you hit the ground running.

Get your copy now and start your SAP GUI Scripting journey today!

SAP GUI Scripting: Understanding the Basics