Interfacing Excel with SAP

Introduction

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
• Production
• Asset management
• Finances
• etc…

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.