Excelden SAP deki fonksiyonu çalıştırarak veri çekme
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
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 “
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.
FUNCTION GET ORDER INFORMATIONS
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
FUNCTION ZPP_GET_ORDER_INFO. *”———————————————————————- *”*”Local Interface: *” IMPORTING *” VALUE(ET_AUFNR) TYPE AUFK-AUFNR OPTIONAL *” TABLES *” ET_PRODUCTIONORDER STRUCTURE ZPP_GET_ORDER_INFO *”———————————————————————- IF NOT ET_AUFNR IS INITIAL. * select order number and material number SELECT AUFK~AUFNR AFKO~PLNBEZ INTO TABLE ET_PRODUCTIONORDER FROM AUFK INNER JOIN AFKO ON AUFK~AUFNR = AFKO~AUFNR WHERE AUFK~AUFNR = ET_AUFNR. ENDIF. <span class="L0S52">ENDFUNCTION</span><span class="L0S55">.</span> |
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.
