Interfacing Excel with SAP
SAP is a world leader in software for integral business administration. It can cope with almost any aspect within a company such as:
- Human resources
- Asset management
for more information about SAP follow the link above…
My experience with SAP is pretty small, actually I have been forced to using SAP in order to issue purchase orders for R&D developments, introduce bill of materials (BOM) of new products and create new components in the Master of materials. These tasks were not performed in a daily basis so I found SAP as a quite harsh environment.
I started digging into the web and found the possibility of interfacing Excel’s macros (VBA) with SAP in order to automate these tasks in a much easier interface. SAP internally is based on a programming lenguage call ABAP which provides loads of functions and procedures to automate tasks. The main problem with ABAP is that you need certain privileged access to SAP in order to be able to program it. Fortunately, these functions are also available to other programming lenguages via an API (called in this context BAPI), and generally, the IT personnel in the companies do not even know about the possibility of this type of access so it is usually opened and unlimited.
Using the abovementioned API, I easily found the connecting way using the following function:
Private Sub SAP_LOGON() '********************************************** 'Create Server object and Setup the connection '********************************************** Set R3 = CreateObject("SAP.Functions") R3.Connection.System = "SAP server" R3.Connection.client = "Client#" R3.Connection.user = "user" R3.Connection.Password = "" R3.Connection.language = "ES" If R3.Connection.Logon(0, False) <> True Then End End If End Sub
Once the connection with SAP is established, I browsed the internet looking for the SAP built-in functions that allow you to access the different aspects (my bible on this can be found here). As an example, the following functions can be easily used:
- To create new materials
- “BAPI_MATERIAL_SAVEDATA” to create new materials in the Master of materials.
- “BAPI_MATERIAL_GETINTNUMBER ” to obtain the next available code in case correlative code numbering is used.
- “CCAP_ECN_CREATE” to create new modification code.
- To create purchase orders (PO)
- “BAPI_PO_CREATE” to create purchase orders.
- “BAPI_PO_RELEASE” to release a PO .
- To operate with the bills of material (BOMs)
- “CSAP_MAT_BOM_CREATE” to create a new BOM.
- “CSAP_MAT_BOM_OPEN” to open a BOM for modifications.
- “CSAP_BOM_ITEM_MAINTAIN” to modify a position of a BOM.
- “CSAP_MAT_BOM_CLOSE” close and save a modified BOM.
- A general function with great use
- “RFC_READ_TABLE” to read some information from any of the tables within SAP. This allows to send SQL queries to the SAP database and retrieve the information easily.
I have to say at this stage that the information about the errors thrown by these functions (and SAP in general) is not really useful and precise so it takes quite a long time to debug the applications looking for errors.
Already developed programs
Up till now, I have developed the following Excel-SAP interaction programs that ease my life when dealing with SAP.
I have all these functions programmed in a macro allowing me to easily create new material, create or update a BOM, create multiple PO given a list of materials to procure and obtain the cost of a list of materials.
Recently, I have developed a new macro that extracts the BOM of a code at all levels; i.e., you obtain the complete list of materials of a code grouped by the field “Group of articles”. This greatly helps when POs for a new prototype are to be issued and helps in controlling further revisions of the bill of materials.
The code for BoM operations and RFC_READ_TABLE:
' EXAMPLE TO EXTRACT THE MANUFACTURER CODE FROM A MATERIAL READING FROM THE MATERIAL'S GENERAL TABLE (MARA) Set objTableContent = R3.Add("RFC_READ_TABLE") With objTableContent .Exports("QUERY_TABLE") = "MARA" ' name of the table to query .Exports("DELIMITER") = "|" End With Set tOptions = objTableContent.Tables("OPTIONS") Set tFields = objTableContent.Tables("FIELDS") Set tData = objTableContent.Tables("DATA") tFields.Rows.Add tFields(1, "FIELDNAME") = "MFRNR" ' Field #1 to retrieve: manufacturer code tFields.Rows.Add tFields(2, "FIELDNAME") = "MTART" ' Field #2 to retrieve: type of material tFields.Rows.Add tFields(3, "FIELDNAME") = "MBRSH" ' Field #3 to retrieve: material branch tFields.Rows.Add tFields(4, "FIELDNAME") = "MATKL" ' Field #4 to retrieve: material group tOptions.Rows.Add SQL_STR = "MATNR EQ '" + padd + code + "'" tOptions(1, "TEXT") = SQL_STR ' SQL query to filter the results returnFunc = objTableContent.Call manufacturer_code = "" If returnFunc = True And tData.RowCount > 0 Then rowdata = Split(tData(1, "WA"), "|") ' if more than 1 row is to be retrieved, these can be scrolled by changing the above line to ' For i = 1 To tData.RowCount 'rowdata = Split(tData(i, "WA"), "|") manufacturer_code = Trim(rowdata(0)) mat_type = Trim(rowdata(1)) ' next End If R3.Remove ("RFC_READ_TABLE")