Connect to SAP R/3 and call custom FM from MS Excel

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.

Introduction

As a SAP consultant I do get lots of questions about master data and how to integrate excel with SAP. SAP had a very limited support to MS Office tools, but in the meantime the integration of MS Office in SAP is very good. But how about the other way around? On the Internet there are lots of specific articles about the integration of SAP in MS Office Excel topic. Most of the topics are about the connectivity/ logon between MS Office Excel and SAP and calling standard BAPI’s or FM’s from MS Office Excel. Although, a good working example is hard to find or the information is outdated. The most popular article(s) on this subject is how to call FM “RFC_READ_TABLE” or “TABLE_ENTRIES_GET_VIA_RFC” from MS Office Excel, parse information from MS Office to one of these FM’s and extract the data into MS Office Excel. However, there are lots of users asking basic questions in these articles/ topics, but the correct answers are missing. Most users are struggling with getting the connectivity between MS Office Excel and SAP R/3 right and extract data from multiple tables instead of one (the above mentioned FM’s can only extract information from one single table). Also there are limitations on the above mentioned FM’s, which in most cases are not even mentioned in the article(s) or topic(s).

The purpose of this post is to give a short demonstration on how:

create a custom FM to extract data from multiple tables in SAP;

create a remote enabled FM;

create IMPORT and TABLES parameters as per requirement from MS Office Excel;

write logic to display material number and its description based on input from the MS Office Excel file;

Save and activate the FM;

to connect from MS Office Excel to SAP R/3, without using references in VBA;

create a MS Office Excel file for user input and input data for the material master;

define output cells in MS Office Excel;

Apply VBA code to connect with SAP R/3;

Read input data for the material master from MS Office Excel;

to display the data from SAP into MS Office Excel.

fetch data from SAP R/3;

display output in MS Office Excel.

Create custom FM to extract data

In this part the creation of a custom FM will be explained. The example will demonstrate how to create a remote enabled FM, parameters, extract data from SAP tables “MARA” and “MAKT” and save and active the custom FM. Of course, I do not need MARA to extract data from MAKT, but I just want to show you an example on how to extract data from two tables in SAP.

Create a remote enabled FM

To create a remote enabled FM you need to be logged into SAP as a developer.

Open transaction SE37 to open the Function Builder. Enter a new name for the custom FM. In this example “ZMM_GET_MM_INFO” will be used as name. Then click on “Create”.

A pop-up will be displayed, containing three fields; Function Module, Function group, Short text and click “Save”.

If you do get the error “Function group XXXX does not exist”, then create a function group with transaction SE80. In this transaction select “Function Group” in the drop down, enter a name and press Enter.

Make sure “Remote-Enabled Module” is selected after creating the function module, this can be found under tab “Attributes”.

Create Import parameters

In this case the material number will be the input (from Excel), so the information can be extracted from SAP. Therefor it is necessary to create import parameters, this can be done in tab “Import”. The information should be extracted from table MARA, so the associated type in this example is MARA-MATNR.

Create table parameters and structure

The next thing would be adding the table parameters, but before entering data in this tab it is necessary to create a structure for the output. This can be done in SE11. Enter the name of the structure in field “Data type” and click on “create”. Then select “structure” in the next screen.

The structure in this example will be easy. There is only need for two fields, MATNR and MAKTX (material number and material description). The structure will look like the figure below. Add the fields and save the structure.

Now, the table parameters can be added in the function module. This can be seen as the output, the data will be imported to the function module and then displayed in Excel. Save the function module and do not forget to activate it. This can be done with button

Add ABAP to FM

Everything is set now and the ABAP code can be added to the function module. Click on tab “Source code” to enter the code.

The code needs to select two fields from a JOIN of tables MARA and MAKT based on the input (ET_MATNR) from excel and return the results in table ET_MATERIAL. The code would look like:

Setup and add code to Excel

As an example two columns have been added to the excel file. One will be the input for SAP (matnr) and second column will return the results from SAP. Make sure the developer tab is visible, if not then please follow the instructions of the following Microsoft website :https://msdn.microsoft.com/en-us/library/bb608625.aspx

Click on the developer tab and click on “Insert”.

Select the first form control “Button” and draw a button on the excel sheet. Enter a name or use the default name and click “new”. In this example I've used "Excel2SAP" as name. After the click Visual basic editor will open automatically and you can start coding.In this case the code in Excel would look something like this (explanation also included in the commented area’s):

It is done, now the function module can be tested in Excel. Let’s see the results:
I’ve renamed my button to “Get Data”. Press on the button. A pop-up will show up, if the user data is empty in the code then you will have to add the user/password etc manually.

Click OK and see the results.

I'll hope this will help you out and you have enjoyed the read. I'll wish you success and luck making great tool with MS Office and SAP. If you have any comments or remarks, please do not hesitate to contact me.

Featured Post

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …