How to Upload Data to SAP from Excel File ?

[Former Member]

Hi all,

I am wondering how to upload data to SAP from excel file using macro.  Can someone help me out?

Thank you.

Regards,

Nur Syabila Zabidi

[marianoc]

Hi Nur Syabila Zabidi,

Using Visual Basic in the excel you can connect to SAP and do what ever you need to do.

For example, the code sent below is:

- Loging the user in SAP

- Running SAP function: MD_STOCK_REQUIREMENTS_LIST_API for a list of material / plant combinations

- Copying the resultant data in the excel..

In this code I am not loading data, but if you have a SAP function to create what you need: materiials, sales orders, etc.. you can do it.

VB code:

Sub SapLogon()

Dim Destination_System As Integer
Dim objBAPIControl As Object
Dim sapConnection, MDPSX As Object
Set objBAPIControl = CreateObject("SAP.Functions")
Set sapConnection = objBAPIControl.Connection

pass = InputBoxDK("Enter SAP GUID Password", Sheets("Logon Data").Cells(11, 3).Value & " System")
Destination_System = Sheets("Logon Data").Cells(11, 2).Value + 2
sapConnection.client = Sheets("Logon Data").Cells(3, Destination_System).Value
sapConnection.hostname = Sheets("Logon Data").Cells(4, Destination_System).Value
sapConnection.Language = Sheets("Logon Data").Cells(5, Destination_System).Value
sapConnection.System = Sheets("Logon Data").Cells(6, Destination_System).Value
sapConnection.SystemNumber = Sheets("Logon Data").Cells(7, Destination_System).Value
sapConnection.User = Sheets("Test Control").Cells(9, 4).Value
sapConnection.Password = pass
sapConnection.Destination = Sheets("Logon Data").Cells(6, Destination_System).Value
If sapConnection.logon(1, True) <> True Then
MsgBox "No connection to R/3!"
Exit Sub
End If

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
   
Set objAccDoc = objBAPIControl.Add("MD_STOCK_REQUIREMENTS_LIST_API")

Set MDPSX = objAccDoc.Tables("MDPSX")
MDPSX.FreeTable

Sheet1.Select
UMat = Sheet1.Range(Range("c11"), Range("c11").End(xlDown)).Rows.Count - 1
Sheet6.Select
UCol = Sheet6.Cells.Find(What:="COUNTID").Column - 1
UCol2 = Sheet6.Range(Range("A1"), Range("A1").End(xlToRight)).Columns.Count
Ufila = Sheet6.Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
If Ufila > 1000000 Then Ufila = 2
Range(Cells(3, 1), Cells(Ufila, UCol2)).ClearContents
Range("a1").Select

ult = 0
For a = 1 To UMat

    objAccDoc.exports("MATNR") = WorksheetFunction.Text(Sheet1.Cells(11 + a, 3), "000000000000000000")
    objAccDoc.exports("WERKS") = WorksheetFunction.Text(Sheet1.Cells(11 + a, 4), "0000")

    returnTable = objAccDoc.Call

If returnTable = True Then

    For i = 1 To MDPSX.RowCount
   
        Sheet6.Cells(ult + i + 1, 1) = Sheet1.Cells(11 + a, 3).Value
        Sheet6.Cells(ult + i + 1, 2) = Sheet1.Cells(11 + a, 4).Value

        For j = 3 To UCol
        Cells(ult + i + 1, j) = Trim(MDPSX(i, "" & Sheet6.Cells(1, j) & ""))
        Next j
    Next i
    ult = ult + MDPSX.RowCount
End If
Next a

Range("N2:Q2").AutoFill Destination:=Range("N2:Q" & ult + 1 & "")
   
   
objBAPIControl.Connection.logoff

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Set sapConnection = Nothing
Set functionCtrl = Nothing
MsgBox "Program finished!", 0, "Exit"
End Sub

Sub SheetOrder()

Dim SortOrder As Variant
Dim Ndx As Long

Application.ScreenUpdating = False

Worksheets("Test Control").Activate
With Range(Range("F24"), Range("F24").End(xlDown).Offset(-1, 0))
    For Ndx = .Cells.Count To 1 Step -1
        Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
    Next Ndx
End With

Application.ScreenUpdating = True

Sheets("Test Control").Select

End Sub

Thanks and Regards,

Mariano

[marianoc]

Hi Nur Syabila Zabidi,

Have you been able to solved your problem?

Do you have any further question? If not please, could you please close the thread by indicating the correct answer or resolution. It could be helpful to others in futures searches on SCN.

Thanks a lot.

Mariano

[tomasz_domanski]

Hi,

Why would you like to use makro in excel to try it? It would be tricky... SAP has got it's own tools for such purposes like LSMW. Have you tried this?

Best Regards,

Tomek