Upload data to SAP tables using excel macros?

[Former Member]

Hello,

I have the excel macro codes to download data from SAP tables which i am sharing below. Can anyone help me in uploading the same data from downloaded excel sheet back to SAP table.

'Code to download

Private Sub cmdGetData_Click()
    Dim oSAP As Object
    Dim oConn As Object
    Dim oFunc As Object
    
    Dim sException As String
    Dim lNumEntries As Long
    Dim oDefTable As Object
    Dim oDataTable As Object
    Dim bReturn As Boolean
    
    Dim sFldName(1 To 2000) As String
    Dim lFldLength(1 To 2000) As Long
    Dim lFldOffset(1 To 2000) As Long
    Dim sFldType(1 To 2000) As String
    Dim lFldCount As Long
    
    Dim lRow As Long
    Dim lCol As Long
    Dim sText As String
        
    If MsgBox("If you choose to continue (OK) then all data in the output tab will be deleted. If you do not want to do that, then please press Cancel", vbDefaultButton2 + vbOKCancel, "Start") = vbCancel Then
        Exit Sub
    End If
    
    Worksheets("Output").Select
    Worksheets("Output").Cells.Select
    'Selection.Delete Shift:=xlRows
    'Selection.Delete Shift:=xlCols
        
    Set oSAP = CreateObject("SAP.Functions")
    Set oConn = oSAP.Connection
    oConn.Applicationserver = Sheets("Selection").Cells(3, 2)
    oConn.SYSTEM = Sheets("Selection").Cells(4, 2)
    oConn.user = Sheets("Selection").Cells(5, 2)
    oConn.Password = Sheets("Selection").Cells(6, 2)
    oConn.client = Sheets("Selection").Cells(7, 2)
    oConn.Language = Sheets("Selection").Cells(8, 2)
    oConn.systemnumber = Sheets("Selection").Cells(9, 2)
    oConn.Tracelevel = 0
    
    'Start
   If Not oConn.logon(0, True) Then
        MsgBox "Logon not successfully!", vbCritical, "Logon"
        GoTo EndProc
    Else
        bReturn = oSAP.RFC_GET_STRUCTURE_DEFINITION(sException, TABNAME:=Sheets("Selection").Cells(11, 2), FIELDS:=oDefTable)
        
        If bReturn Then
            For lRow = 1 To oDefTable.Rows.Count
                sFldName(lRow) = oDefTable.Rows(lRow).Value("FIELDNAME")
                lFldLength(lRow) = oDefTable.Rows(lRow).Value("INTLENGTH")
                lFldOffset(lRow) = oDefTable.Rows(lRow).Value("OFFSET")
                sFldType(lRow) = oDefTable.Rows(lRow).Value("EXID")
            Next
            lFldCount = oDefTable.Rows.Count
        Else
            Select Case sException
                Case "TABLE_NOT_ACTIVE"
                    MsgBox "TABLE_NOT_ACTIVE!", vbCritical, "Get Data"
                Case Else
                    MsgBox "Invalid table access!", vbCritical, "Get Data"
            End Select
            
            GoTo EndProc
        End If
        
        'TABLE
        bReturn = oSAP.RFC_GET_TABLE_ENTRIES(sException, BYPASS_BUFFER:=" ", FROM_KEY:=" ", GEN_KEY:=" ", MAX_ENTRIES:=0, TABLE_NAME:=Sheets("Selection").Cells(11, 2), TO_KEY:=" ", NUMBER_OF_ENTRIES:=lNumEntries, ENTRIES:=oDataTable)
        
        If bReturn Then
            For lCol = 1 To lFldCount
                Sheets("Output").Cells(1, lCol) = sFldName(lCol)
                If sFldType(lCol) = "C" Then
                    Sheets("Output").Columns(lCol).Select
                    Selection.NumberFormat = "@"
                End If
            Next
            
            For lRow = 1 To oDataTable.Rows.Count
                For lCol = 1 To lFldCount
                    sText = Trim(Mid(oDataTable.Rows(lRow).Value(1), lFldOffset(lCol) + 1, lFldLength(lCol)))
                    sText = Replace(sText, Chr(34), Chr(34) & Chr(34))
                    Sheets("Output").Cells(lRow + 1, lCol) = sText
                Next
            Next
        Else
            Select Case sException
                Case "TABLE_NOT_FOUND"
                    MsgBox "Table not found!", vbCritical, "Get Data"
                Case "TABLE_EMPTY"
                    MsgBox "Table Empty!", vbCritical, "Get Data"
                Case Else
                    MsgBox "Invalid data access!", vbCritical, "Get Data"
            End Select
            
            GoTo EndProc
        End If
    End If




    Call NiceLayout(oDataTable.Rows.Count + 1, lFldCount)

    'End
    MsgBox "Data successfully downloaded!", vbInformation, "Download"
    
EndProc:
    On Error Resume Next
    Set oSAP = Nothing
    Set oConn = Nothing
    Set oDefTable = Nothing
    Set oDataTable = Nothing
End Sub

Edited by: Rakeshk13 on Sep 1, 2010 12:06 PM

[michael_hobbins]

Hello, I checked the function group containing the 2 functions modules you used and none of them are meant to upload data. Searched for other function modules and counldn't find any either.

A way around would be to

1. develop a function module which will insert or append records into the table you desire. the main parameter will be a table conveniently with the same structure of the destination table

2. the excel macro will call the developed function module. It'll add records to the table parameter reading the rows in your excel spreadsheet

Skills needed: ABAP + Excel macros

Precondition: destination table must exist; you know the destination table's structure;

Cheers,

Michael