How to connect to SAP and Call custom Function Module from Microsoft Excel

171

How to connect to SAP and Call custom Function Module from Microsoft Excel

Purpose

  • Create a custom Function Module to extract data from single table or multiple tables in SAP. (Example: Material, Bill of Materials, Routing, Production Order, etc … )
  • Connect from MS Office Excel to SAP R/3, without using references in VBA
  • Display the data from SAP into MS Office Excel

1.Create custom Function Module to extract data

Example: Need extract data from SAP tables “AUFK” and “AFKO

AUFK Order Master Data
AFKO Order Header Data PP Orders

2.Create a remote enabled Function Module

Log on SAP GUI with sap development User

Ensure your user must be development user. User have authorization access key.

Go to transaction SE37  – Function Builder >> Enter a name of new Function Module.

Example ZPP_GET_ORDER_INFO >> Click Create.

Enter Function Module, Function group, Short text and click Save.

How to create Function Group

Go to transaction code: SE37 >> Choose Tab Goto >> Choose Function Groups >> Click Create Group or click Find Group to find any exist group .

Fill name of Function Group, Short Text >> Click Save.

IF system require one access key. Fill Access key and save on package, save on request and finish.

System message ” Function group ZPP_FM created “

Contact basis adminstrator to get access key for ABAP development User. Each User and installation number of system will generate one access key. 

Choose tab Attributes >> Check  Remote-Enabled Module

3. Create Import parameters

Input of function module is production order code (number) from Excel. Choose tab Import .The information extract from table AUFK, so the associated type is AUFK-AUFNR. 

Fill Parameter Name, Type, Associated Type, Short text, Check Optional & Pass Value

4. Create table parameters and structure

Create table parameters, create structure for output.

Go to transaction code: SE11 >> Enter the name of the structure in field Data type and click on Create. Then select Structure in the next screen.

The structure need two fields AUFNR and KTEXT (order number and description order). Fill Short Description , Insert the fields >> click Save. (save on package and request)

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 ‘active’.

5. 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.

We need join two tables AUFK and AFKO.

We are done in SAP, so let’s continue in Excel.

6.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 (AUFNR) 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 :

Microsoft Link to show developer tab

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.

It is done, now the function module can be tested in Excel.

Rename button to Get Data From Production Order. Press on the button.

Click OK and see the results.

LEAVE A REPLY

Please enter your comment!
Please enter your name here