Data Upload into SAP from Microsoft Excel – VBA Part

[Former Member]

Data Upload into SAP from Microsoft Excel – VBA Part

In my previous weblog kathirvel.balakrishnan2/blog/2006/05/08/data-upload-into-sap-from-microsoft-excel-150-abap-part for the sample application to post material data in SAP was discussed.

The operation like
1. Establishing a Connection to SAP
2. Populating the import parameters of an RFC Function module
3. Executing the RFC function and processing the result

are to be handled in the Microsoft Excel. This can be achieved using the Visual Basic for Applications.

The Excel VBA reference is available for free download. For more information about this language please visit MSDN.

Open a new Excel Workbook and add the Visual Basic and Control Box



Add a command button to the workbook and update it properties as shown below





One main thing is to be noted at this point. The initial data row in the excel sheet is fixed to ROW5 in this example.



In case if you want to change, just put the required start row in the FOR loop.

For vRows = 5 To vLastRow [Replace 5 with your current data begin row]

Now double click the Command button, this will take you to the VBA editor, Place the below code in the Global Declarations

Option Explicit' Declaration for the necessary objectsDim objBAPICortrol, objConnection, objCreateMaterial, objMaterial, objMaterialDescription, objIndustrySector, objMaterialType, objMaterialGroup, objBaseUnit, objDivision, objReturn As ObjectDim vLastRow, vRows As Integer

And the main logic goes as below

Private Sub BatchMaterialUpload_Click()' Getting the last filled Row in Column AvLastRow = Cells(Rows.Count, "A").End(xlUp).Row' Setting the necessary variables for R/3 connectionSet objBAPICortrol = CreateObject("SAP.Functions")Set objConnection = objBAPICortrol.Connection' Establish a connectionIf objConnection.Logon(0, False) Then    MsgBox "Connection Established"End IfOn Error Resume Next' Assign the ParametersSet objCreateMaterial = objBAPICortrol.Add("ZRFC_CREATE_NEW_MATERIAL")Set objMaterial = objCreateMaterial.Exports("MATERIAL")Set objMaterialDescription = objCreateMaterial.Exports("MATL_DESC")Set objIndustrySector = objCreateMaterial.Exports("IND_SECTOR")Set objMaterialType = objCreateMaterial.Exports("MATL_TYPE")Set objMaterialGroup = objCreateMaterial.Exports("MATL_GROUP")Set objBaseUnit = objCreateMaterial.Exports("BASE_UOM")Set objDivision = objCreateMaterial.Exports("DIVISION")' Now looping through all values create materials' The data begin row is set to 5For vRows = 5 To vLastRow' Set the Parameter ValuesobjMaterial.value = ThisWorkbook.ActiveSheet.Cells(vRows, 1).valueobjMaterialDescription.value = ThisWorkbook.ActiveSheet.Cells(vRows, 2).valueobjIndustrySector.value = ThisWorkbook.ActiveSheet.Cells(vRows, 3).valueobjMaterialType.value = ThisWorkbook.ActiveSheet.Cells(vRows, 4).valueobjMaterialGroup.value = ThisWorkbook.ActiveSheet.Cells(vRows, 5).valueobjBaseUnit.value = ThisWorkbook.ActiveSheet.Cells(vRows, 6).valueobjDivision.value = ThisWorkbook.ActiveSheet.Cells(vRows, 7).value' Function callobjCreateMaterial.call' Get return parameters & display in excelSet objReturn = objCreateMaterial.Imports("RETURN")ActiveSheet.Cells((vLastRow + vRows), 1) = objReturn.value("MESSAGE")' Error handlingIf Err Then    MsgBox Err.DescriptionEnd IfNext vRowsEnd Sub

The example is created in such a way that the return messages will be populated in the same sheet. Thus we must know the last filled data row to that we can start writing the return messages after that row.

' Getting the last filled Row in Column A
vLastRow = Cells(Rows.Count, "A").End(xlUp).Row

The above code will determine the last filled row in the Excel sheet.

Now populating the return messages will be done as below

' Get return parameters & display in excel
Set objReturn = objCreateMaterial.Imports("RETURN")
ActiveSheet.Cells((vLastRow + vRows), 1) = objReturn.value("MESSAGE")



Thus the return messages can be easily populated as shown above.

Hope this example was useful, and using the same logic much more complex data uploads can be done.