0

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:

  1. Connect to the SAP GUI
  2. Run a TCode (typically a report)
  3. Enter selection criteria
  4. Get the resulting data
  5. 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?

Some details:

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[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

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.