Back in the days when I had a mundane job as a management accountant I discovered I could take the monotony out of working with SAP (back then 3.1h) by incorporating my beloved Excel spreadsheets through the use of Business Application Process Interfaces (BAPI). A BAPI in this case also refers to a ‘remote enabled’ function module.
There are thousands of BAPI / Remote Function Modules available in SAP (ECC, CRM, Netweaver), these can be listed by pulling up table TFDIR and placing a ‘R’ for remote in field FMODE.
However the only function modules that remain stable from SAP release to release are BAPI (funnily enough these can be listed via t-code BAPI). SAP promises that all BAPI will be forward compatible and to date I have found this to be true.
This blog gives an introduction on how to integrate Excel spreadsheets (or any MS product that supports VBA) with an SAP system.
In order to utilize function calls from MS products you will need to have the SAP GUI installed on your local PC as this will provide the appropriate dll’s needed for connection.
How to connect MS Excel to an SAP System:
That was the most difficult bit; this code can be re-used in ALL applications requiring connection from MS VBA to an SAP backend.
The next step is to select the required function module, in this example I use FM RFC_READ_TABLE, this can be found in all SAP ‘ABAP’ Systems. Although not a true BAPI it is remote enabled and I have found that it hasn’t changed a great deal from early releases.
Calling the Function Module/BAPI from VBA
This Function Module contains 4 input or ‘importing’ parameters and 2 output or ‘exporting’ parameters. SAP Function Modules are well documented and a great deal of information can also be found on SDN.
In this example I will be retrieving data from Table T000 which can be found in all SAP system, this is the client table.
That is it your first (and hopefully not last) remote call from an MS office application to SAP. In essence this is how the BEx (an excel add-in for BW) communicates with the backend to retrieve query information.
I have found numerous uses for the application of the described code, these include but by no means limited to:
- Upload (Batch) of FI document postings
- Retrieval of systems statistics & metadata from BW.
- Creation of 100’s of user accounts
- Writing/reading information from BW back to ECC – a kind of integrated planning mechanism
- To automate uploads of flat files into BW, one click solutions
- To reconcile data and configuration across system landscapes