VBA is a general way to retrieve data from SAP

Question

Does anyone know how to use VBA to retrieve data from SAP Netweaver?

I have several daily reports that require exporting data from SAP to Excel and formatting them into a report. I already wrote working VBA macros that do formatting. I have to manually extract the data and run each report macro separately. So much time could be saved if my macro could just go to SAP, capture data for report No. 1, format it, capture data for report No. 2, etc.

I work with SAP NetWeaver (release 730, version 7300.1.3.1079). Reports are tables and charts of Excel PivotTables.

Thanks!

Answer 1

It all depends on how you access your SAP system. An ABAP program that exports data and / or RFC that your macro can invoke to directly retrieve data or create an SAP file is probably best.

However, as a rule, people who are looking for such an answer are looking for an immediate solution that does not require their IT department to spend months setting up their SAP system.

In this case, you probably want to use SAP GUI Scripting. SAP GUI scripts let you automate the Windows SAP GUI in the same way that you automate Excel. In fact, you can invoke the SAP GUI directly from the Excel macro. Read more here . The SAP GUI has a tool for recording macros, as Excel does. It writes macros to VBScript, which is almost identical to Excel VBA and can usually be copied and pasted directly into an Excel macro.

Code example

Here is a simple example based on an SAP system that I have access to.

Public Sub SimpleSAPExport() Set SapGuiAuto = GetObject("SAPGUI") 'Get the SAP GUI Scripting object Set SAPApp = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI Set SAPCon = SAPApp.Children(0) 'Get the first system that is currently connected Set session = SAPCon.Children(0) 'Get the first session (window) on that connection 'Start the transaction to view a table session.StartTransaction "SE16" 'Select table T001 session.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").Text = "T001" session.findById("wnd[0]/tbar[1]/btn[7]").Press 'Set our selection criteria session.findById("wnd[0]/usr/txtMAX_SEL").text = "2" session.findById("wnd[0]/tbar[1]/btn[8]").press 'Click the export to file button session.findById("wnd[0]/tbar[1]/btn[45]").press 'Choose the export format session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select session.findById("wnd[1]/tbar[0]/btn[0]").press 'Choose the export filename session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "test.txt" session.findById("wnd[1]/usr/ctxtDY_PATH").text = "C:\Temp\" 'Export the file session.findById("wnd[1]/tbar[0]/btn[0]").press End Sub 

Script Record

To find the names of elements like wnd[1]/tbar[0]/btn[0] , you can use a script entry. Click the "Configure Local Layout" button, it probably looks something like this:
Then find the menu item script Record and play.

In this case, the More button allows you to see / modify the file in which the VB script is written. The output format is a little dirty, it records things like selecting text, clicking inside a text field, etc.

Edit: early and late binding

The provided script should work if it is copied directly to the VBA macro. It uses the last binding, the Set SapGuiAuto = GetObject("SAPGUI") defines the SapGuiAuto object.

If you want to use early binding so that your VBA editor can show the properties and methods of the objects you use, you need to add a link to sapfewse.ocx to the SAP GUI installation folder.