VBA general way for pulling data out of SAP

Question

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

I have a number of daily reports that require exporting data from SAP to Excel and formatting it into a report. I have already written working VBA macros that do the formatting. I have to manually extract the data and run each report macro individually. So much time could be saved if my macro could just go into SAP, grab the data for report #1, format it, grab the data for report #2, etc.

I work with SAP NetWeaver (Release 730, Version 7300.1.3.1079). The reports are just Excel pivot tables and charts.

You don't specify the product you export data from (SAP is a company that ships a gazillion of products - which one are you talking about?). You don't specify what reports or what reporting technique you base your formatting on. It's hard to answer this question without more information. — vwegert, Oct 18 '13 at 15:18
I think this may help You in case of SAP connection https://stackoverflow.com/a/58348742/11636588 or whole article https://simpleexcelvba.com/connect-to-sap-via-excel-vba/ — Teamothy, Nov 30 '19 at 19:42

AndASM · Accepted Answer · 2014-10-28T19:51:33.450

This all depends on what sort of access you have to your SAP system. An ABAP program that exports the data and/or an RFC that your macro can call to directly get the data or have SAP create the file is probably best.

However as a general rule people looking for this sort of answer are looking for an immediate solution that does not require their IT department to spend months customizing their SAP system.

In that case you probably want to use SAP GUI Scripting. SAP GUI scripting allows you to automate the Windows SAP GUI in much the same way as you automate Excel. In fact you can call the SAP GUI directly from an Excel macro. Read up more on it here. The SAP GUI has a macro recording tool much like Excel does. It records macros in VBScript which is nearly identical to Excel VBA and can usually be copied and pasted into an Excel macro directly.

Example Code

Here is a simple example based on a SAP system 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 Recording

To help find the names of elements such aswnd[1]/tbar[0]/btn[0] you can use script recording. Click the customize local layout button, it probably looks a bit like this:
Then find the Script Recording and Playback menu item.

Within that the More button allows you to see/change the file that the VB Script is recorded to. The output format is a bit messy, it records things like selecting text, clicking inside a text field, etc.

Edit: Early and Late binding

The provided script should work if copied directly into a VBA macro. It uses late binding, the line Set SapGuiAuto = GetObject("SAPGUI") defines the SapGuiAuto object.

If however you want to use early binding so that your VBA editor might show the properties and methods of the objects you are using, you need to add a reference to sapfewse.ocx in the SAP GUI installation folder.

Thanks! I'm going to do some experimenting with Script Recording today. For what I'm trying to do, it sounds like the best option (especially since the SAP DB I work with is owned by a customer and their IT depatment isn't going to do anything on my behalf). — Kes Perron, Oct 21 '13 at 13:47
The link you provided to the SAP GUI Scripting page had a lot of info on writing scripts, but I couldn't find anything about how to call the SAP GUI with VBA. I'm assuming I can't just copy/paste the script verbatim. Is there a specific reference library I should enable? Do I need to declare variables like SapGuiAuto? — Kes Perron, Oct 21 '13 at 18:05
You should be able to copy the script verbatim. The script is using late binding. I'll edit the answer with details on early and late binding for this. — AndASM, Oct 24 '13 at 15:25
I found and fixed a typo in the code that would prevent it from running. Funny that no one has commented on that :) — AndASM, Oct 28 '14 at 19:51
Here's another good page that shows how to enable the Script Recording and Playback menu item. [Script Recording & Playback for Dummies](http://scn.sap.com/docs/DOC-57020) — Profex, Nov 14 '14 at 16:50
@AndASM Hey! While this looks promising, i can't `Set SAPCon = SAPApp.Children(0)` - since there are no children objects when i examine `SAPApp`. Part of the problem might be that `SAPapp.ActiveSession = Nothing`, even when i have a session open. Any ideas? — Martin Dreher, Nov 17 '16 at 10:09
@MartinDreher that means there is no open session, at least not in the SAP application object you opened. Try opening a new one and see what happens. I use a line like `Set SAPCon = SAPApp.OpenConnection("The name from the name column in the SAP login window that I double click on goes here", True, True)` to open a new connection. — AndASM, Jan 30 '17 at 20:39
@AndASM Is there a solution to the prerequisite that SAP GUI must be running in order to be able to run the script? I can think of a SAP daemon process that starts with each OS boot - is there such a "SAP daemon"? — Aliakbar Ahmadi, Oct 26 '17 at 08:20

VBA general way for pulling data out of SAP

1 Answers1

Example Code

Script Recording

Edit: Early and Late binding

Linked

Related