My team often needs to extract data from the SAP R3 GUI into a file format such as Excel. We have been using VBA scripting to do this, using the approach described in https://stackoverflow.com/questions/19452461.
Each extract has a similar format:
- Connect to the SAP GUI
- Run a TCode (typically a report)
- Enter selection criteria
- Get the resulting data
- Export to Excel
This is a relatively complicated process to code because you need to know the names of each GUI element that you need to set. Also, SAP sometimes initializes these with default values that need to be cleared.
My question: could a generalized tool be created? If so, does it already exist?
Since the fields in the SAP GUI are named using a consistent naming convention, and API calls exist to iterate over all objects displayed in a screen, I could create a tool that performs all tasks above, but instead of coding each step in the process, create a data structure which captures the data needed to do an export -- e.g. TCode, field names (in business terms), and any values to update.
For example, here's the traditional way of performing an extract:
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/usr/ctxtDATABROWSE-TABLENAME").Text = "T001" session.findById("wnd/tbar/btn").Press 'Set our selection criteria session.findById("wnd/usr/txtMAX_SEL").text = "2" session.findById("wnd/tbar/btn").press 'Click the export to file button session.findById("wnd/tbar/btn").press 'Choose the export format session.findById("wnd/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select session.findById("wnd/tbar/btn").press 'Choose the export filename session.findById("wnd/usr/ctxtDY_FILENAME").text = "test.txt" session.findById("wnd/usr/ctxtDY_PATH").text = "C:\Temp\" 'Export the file session.findById("wnd/tbar/btn").press
Instead, create a VBA function (and associated subroutines) called
extractExcel which can interpret the following data structure and perform the actions above:
Dim extractSAPParams As Collection Dim curReportInfo as reportInfoType Sub main() initParams curReportInfo.TCode = "SE16" setParam "Table", "MARA", "" setParam "Plant", 1000, 2000 setParam "Material", "ABC", "XYZ" setParam "c", #3/1/2019#, #4/30/2019# runSAP End Sub
The tool would consist primarily of a module which would contain the functions/subroutines below:
Type reportInfoType TCode as String outputFileName as String outputFilePath as String End Type Sub initParams(pReportInfo) Set extractSAPParams = New Collection if pReportInfo.outputFileName = "" then pReportInfo.outputFileName = "Export.xlsx" end if if pReportInfo.outputFilePath = "" then pReportInfo.outputFilePath = <code to get path to default SAP export folder> end if End Sub Function setParam(pName, pLowValue, pHighValue) As String() Dim param(3) As String param(1) = pName param(2) = pLowValue param(3) = pHighValue extractSAPParams.Add param, pName End Function Sub runSAP() Dim p <code to clear all fields> 'For Each p In extractSAPParams ' Debug.Print p(1), p(2), p(3) 'Next <code to find the needed element by scanning all elements> <code to submit report and save output> End Sub
This could be extended to support more complex parameters, such as lists of values.
Clearly this tool would have limits (e.g. if there were a complex set of parameter screens, or non-standard ways of exporting data). However, for a bulk of the functions we run, this could save development and maintenance time.
I searched GitHub for "SAP VBA" and didn't find anything.
Thanks in advance for any feedback you may have!