how do i display field names with RFC_READ_TABLE in VBA

[Former Member]

Hi all,

I'm new to SAP and VBA and would like to display tables in Excel via RFC_READ_TABLE. This is functiong so far, however i'd also like to display the table heade / field name in the first row to make better use of the data. Unfortunately i've not been able to figure it out. Here's my code so far.

'***********************************
' Allgemeine SAP Objekte
'***********************************
Public oConSMD As Object
Public FuncSMD As Object
Public oLogSMD As Object
Public SMDFunctions As Object
'***********************************
' Objekte für RFC_READ_TABLE
'***********************************
Public SMDFunc As Object
Public SMDTabObj As Object
Public LOGSSMD As Integer
Public SMDdata() As String

Private Sub Logon()

    Dim lv_cell As Range

    Rem *** Create the Logon Control
    Set oLogSMD = CreateObject("SAP.Logoncontrol.1")
    Rem *** Get a connection object
    Set oConSMD = oLogSMD.NewConnection
    Rem *** Either all parameters are set by the design environment
    Rem *** (except the password) or they are hardcoded,as in the following
    Rem *** lines. The following lines depend on your R/3 environment
    Rem *** These are the system parameters
    oConSMD.System = "systemname"
    oConSMD.ApplicationServer = "hostname"
    oConSMD.SystemNumber =        
    Rem ***User specific data
    oConSMD.User = "username"
    oConSMD.Client = 100
    oConSMD.Language = "DE"

    Rem ***Try to connect to the R/3 System
    If oConSMD.Logon(0, False) = False Then
        MsgBox "R/3 connection failed"
     LOGSSMD = 0
    Exit Sub
    End If

    LOGSSMD = 1
    Set SMDFunctions = CreateObject("SAP.Functions")
    'Log on to the R/3 System.
    Set SMDFunctions.Connection = oConSMD
    Set SMDFunc = SMDFunctions.Add("RFC_READ_TABLE")

End Sub

Private Sub LogoffSMD()

  If LOGSSMD = 1 Then oConSMD.Logoff

End Sub


Sub SMDRead_Table(Table As String)

Dim T() As String
Dim i As Long
Dim k As Long

SMDFunc.Exports("DELIMITER") = vbTab
'SMDFunc.Exports("NO_DATA") = "X"
SMDFunc.Exports("QUERY_TABLE") = Table
'SMDFunc.Exports("ROWCOUNT") = "500"

'******** Create Table Object ************
Set SMDTabObj = SMDFunc.Tables("FIELDS")
SMDTabObj.freetable

'SMDTabObj.appendrow
'SMDTabObj.appendrow
'SMDTabObj.cell(1, 1) = "CHECKTABLE"
'SMDTabObj.cell(2, 1) = "KEYFLAG"

'******** Create Table Object ************
Set SMDTabObj = SMDFunc.Tables("OPTIONS")
SMDTabObj.freetable
'SMDTabObj.appendrow
'SMDTabObj.cell(1, 1) = "TABNAME EQ '" & Tab_Nam & "' AND LENG NE 0"

'******** Create Table Object ************
Set SMDTabObj = SMDFunc.Tables("DATA")

'******** CLear Table ************
SMDTabObj.freetable

If SMDFunc.Call = True Then
'******** Read Result ************
T = Split(SMDTabObj.cell(1, 1), vbTab)

ReDim SMDdata(SMDTabObj.Rows.Count, UBound(T))


x = UBound(T)


k = 0
For Each Element In SMDTabObj.Rows
    T = Split(Element("WA"), vbTab)
    For i = 0 To x
     SMDdata(k, x - i) = T(i)
    Next i
    k = k + 1
Next Element

Else
    MsgBox SMDFunc.Exception
End If

'*** ohne Mandant
ReDim Preserve SMDdata(SMDTabObj.Rows.Count, UBound(T) - 1)

End Sub



Sub Start()

Dim i As Long
Dim k As Long

Sheets(1).Select

Logon

If LOGSSMD = 1 Then
    SMDRead_Table ("T001W")

    For i = 0 To UBound(SMDdata, 1)
        For k = 0 To UBound(SMDdata, 2)
            Cells(i + 1, UBound(SMDdata, 2) - k + 1).Value = SMDdata(i, k)
    Next k, i

    LogoffSMD

End If

End Sub

Kind regards,

moritz heeg

[holger_khn · Accepted solution]

Hi Moritz.

To give you an idea check this code:

For intCol = 1 To tblCHAR_VALUES.ColumnCount

     ThisWorkbook.Sheets("CHAR_VALUES").Cells(1, (intCol + 1)).Value = tblCHAR_VALUES.ColumnName(intCol)

     ThisWorkbook.Sheets("CHAR_VALUES").Cells(1, (intCol + 1)).Interior.ColorIndex = 6

Next

I walk over all columns and get olumnNames from them which I use for first row in my Excel sheet.

[Former Member · Accepted solution]

Hi Holger,

I'm not sure I understand. I want to import the SAP Table Fieldnames into the first Column of the document but it can't think of a way to access those and then import them properly.

I know that with" Set SMDTabObj = SMDFunc.Tables("FIELDS")  " I can access them but I don't know how to use this.

Do you know how I could do that?

Thank you for your answer.

[holger_khn · Accepted solution]

Hi Moritz.

I have modified your code and test in our Environment. So you can take it as Initial draft. Just modify to your requirements:

'***********************************
' Allgemeine SAP Objekte
'***********************************
Public oConSMD As Object
Public FuncSMD As Object
Public oLogSMD As Object
Public SMDFunctions As Object
'***********************************
' Objekte für RFC_READ_TABLE
'***********************************
Public SMDFunc As Object
Public SMDTabObj As Object
Public SMDTabObjFields As Object
Public LOGSSMD As Integer
Public SMDdata() As Variant
Private Sub Logon()
    Dim lv_cell As Range

    Rem *** Create the Logon Control
    Set oLogSMD = CreateObject("SAP.Logoncontrol.1")

    Rem *** Get a connection object
    Set oConSMD = oLogSMD.NewConnection

    Rem ***Try to connect to the R/3 System
    If oConSMD.Logon(0, False) = False Then
        MsgBox "R/3 connection failed"
     LOGSSMD = 0
    Exit Sub
    End If

    LOGSSMD = 1
    Set SMDFunctions = CreateObject("SAP.Functions")
    'Log on to the R/3 System.
    Set SMDFunctions.Connection = oConSMD
    Set SMDFunc = SMDFunctions.Add("RFC_READ_TABLE")
End Sub
Private Sub LogoffSMD()
  If LOGSSMD = 1 Then oConSMD.Logoff
End Sub
Sub SMDRead_Table(Table As String)
Dim T() As String
Dim i As Long
Dim k As Long
SMDFunc.Exports("DELIMITER") = vbTab
'SMDFunc.Exports("NO_DATA") = "X"
SMDFunc.Exports("QUERY_TABLE") = Table
'SMDFunc.Exports("ROWCOUNT") = "500"
'******** Create Table Object ************
Set SMDTabObjFields = SMDFunc.Tables("FIELDS")
SMDTabObjFields.freetable
'******** Create Table Object ************
Set SMDTabObj = SMDFunc.Tables("OPTIONS")
SMDTabObj.freetable
'******** Create Table Object ************
Set SMDTabObj = SMDFunc.Tables("DATA")
'******** CLear Table ************
SMDTabObj.freetable
If SMDFunc.Call = True Then
'******** Read Result ************
Application.ScreenUpdating = False
    If SMDTabObjFields.RowCount > 0 Then
        ThisWorkbook.Sheets("Sheet1").Activate
        For i = 1 To SMDTabObjFields.RowCount
            ThisWorkbook.Sheets(1).Cells(1, i).Value = SMDTabObjFields(i, "FIELDNAME")
            ThisWorkbook.Sheets(1).Cells(2, i).Value = SMDTabObjFields(i, "FIELDTEXT")
        Next

        Columns.AutoFit
    End If

    k = 3

    For Each Element In SMDTabObj.Rows
        T = Split(Element("WA"), vbTab)
        X = UBound(T)
        For i = 1 To X
         ThisWorkbook.Sheets(1).Cells(k, (i + 1)).Value = T(i)
        Next i
        k = k + 1
        Columns.AutoFit
    Next Element
    Else
        MsgBox SMDFunc.Exception
    End If
Application.ScreenUpdating = True
End Sub
Sub Start()
Dim i As Long
Dim k As Long
Sheets(1).Select
Logon
If LOGSSMD = 1 Then
    SMDRead_Table ("T001W")
    LogoffSMD
End If
End Sub

[Former Member · Accepted solution]

Hi Holger

Thank you very much, I see were I went wrong now.

You were very helpful.

Kind regards,

Moritz.