SAP R/3 <=> Excel : Extract data from SAP TRANSACTION to EXC

[BonVivant]

G'day,

Warning: Very green, wet behind the ears SAP prime here. So not sure if this question belongs here.
To any and all generous providers of information - please be kind and use small words

Background info:
We have designed a dashboard where RAW data is coming from various databases... for which one of the databases is SAP
The dashboard operates by extracting data from said databases via EXCEL VBA macros... the EXCEL dashboard does it "metrics" calculation within EXCEL via EXCEL functions and macros.
Now.. we need to get data from SAP and into EXCEL.
I've just installed SAP Logon PAD 720. So I have access to SAP via Logon PAD
For testing purposes I obtained access to a table ZIXIIL2_T03 (ZE16) via the SAP Logon PAD and I am able to extract data from said table.
I have been able to extract SAP data from a "Transparent" table via EXCEL VBA macro ( ie: No need to use SPA Logon PAD). See code below.

Now.. question is: How can I convert code below to extract data from a ""Struct" TABLE-?

Below is code to extract data from a "Transparent" TABLE
Sorry comments are in french

Working EXCEL can be found here:
http://www.saptechno.com/telechargement ... ml?start=5

Code: Select all

Option Explicit

Sub RFC_VB()

' Author Abdel SIDHOM - 2005
' Version : 9.06
' http://www.saptechno.com

' Variables
Dim SAP_Func        As Object         ' Fonctions SAP
Dim MY_FUNC         As Object         ' Fonction spécifique
Dim CONN            As Object         ' Chaîne de connexion
Dim RESULT          As Boolean
Dim TABNAME         As Object
Dim ROWCOUNT        As Object
Dim ROWSKIPS        As Object
Dim RC              As Object
Dim Table_FIELDS    As Object
Dim Table_OPTIONS   As Object
Dim Table_DATA      As Object
Dim i, j, index_ligne, pass, Nb_enreg, StartRow, Start, Lenght As Integer
Dim Out_XLS         As String
Dim FileName        As String
Dim FileDelimiter   As String
Dim Output_to As String
Dim Output_file As String
Dim File_sep As String
Dim File_line As String


' Création d'une nouvelle connexion
Set SAP_Func = CreateObject("SAP.Functions")
SAP_Func.logfilename = "c:\temp\rfc_read_table.txt"
'SAP_Func.loglevel = 3 '9 pour avoir un max d'infos

Set CONN = SAP_Func.Connection

CONN.tracelevel = 0 'Niveau de trace côté SAP 0 ou 1

' Login
If CONN.logon(0, False) <> True Then
    MsgBox "Impossible de se connecter!."
    Exit Sub
End If

' Initialisation les objets d'import et d'export de la fonction
Set MY_FUNC = SAP_Func.Add("RFC_READ_TABLE")
Set TABNAME = MY_FUNC.Exports("QUERY_TABLE")
Set ROWCOUNT = MY_FUNC.Exports("ROWCOUNT")
Set ROWSKIPS = MY_FUNC.Exports("ROWSKIPS")

Set Table_FIELDS = MY_FUNC.Tables("FIELDS")
Set Table_OPTIONS = MY_FUNC.Tables("OPTIONS")
Set Table_DATA = MY_FUNC.Tables("DATA")

TABNAME.Value = Sheets("OPTIONS").Range("B1").Value
ROWCOUNT.Value = Sheets("OPTIONS").Range("B2").Value

' Remplissage de la table Table_FIELDS à partir de l'onglet OPTIONS
i = 9
index_ligne = 1
Sheets("OPTIONS").Select
While i < 60    ' limité à 50 lignes
    If Trim(Cells(i, 1).Value) <> "" Then
        Table_FIELDS.AppendRow
        Table_FIELDS(index_ligne, 1) = Trim(Cells(i, 1).Value)
        index_ligne = index_ligne + 1
    End If
    i = i + 1
Wend

' Remplissage de la table Table_OPTIONS à partir de l'onglet OPTIONS
i = 9
index_ligne = 1
While i < 60    ' limité à 50 lignes
    If Trim(Cells(i, 2).Value) <> "" Then
        Table_OPTIONS.AppendRow
        Table_OPTIONS(index_ligne, 1) = Trim(Cells(i, 2).Value)
        index_ligne = index_ligne + 1
    End If
    i = i + 1
Wend



' Netoyage de l'onglet DONNEES
Sheets("DATA").Select
Cells.Delete Shift:=xlUp
Range("A1").Select


Output_to = Sheets("OPTIONS").Cells(3, 2).Value
Output_file = Sheets("OPTIONS").Cells(4, 2).Value
File_sep = Sheets("OPTIONS").Cells(5, 2).Value

If (Output_to = "FILE") Then
    Open Output_file For Output As #1
End If

pass = 0
RESULT = True
Nb_enreg = 1
StartRow = 2
While (RESULT = True And Nb_enreg > 0)

    pass = pass + 1
    ' Appel de la fonction
    Table_DATA.Freetable
    ROWSKIPS.Value = ROWCOUNT.Value * (pass - 1)
    RESULT = MY_FUNC.Call
   
    If RESULT <> True Then
         MsgBox MY_FUNC.EXCEPTION
         SAP_Func.Connection.logoff
         Exit Sub
    End If

   
    If pass = 1 Then
        ' Affichage de l'entête au premier passage
        File_line = ""
        For i = 1 To Table_FIELDS.ROWCOUNT
            If (Output_to = "EXCEL") Then
                Cells(1, i).Value = Table_FIELDS(i, "FIELDNAME")
                Cells(1, i).AddComment
                Cells(1, i).Comment.Visible = False
                Cells(1, i).Comment.Text Text:=Table_FIELDS(i, "FIELDTEXT") & Chr(10) & ""
                Else
                    File_line = File_line & Table_FIELDS(i, "FIELDTEXT") & " (" & Table_FIELDS(i, "FIELDNAME") & ")" & File_sep
                End If
               
        Next i
         If (Output_to = "FILE") Then
            Print #1, File_line
        End If
    End If

    ' Récupération des données
    Nb_enreg = Table_DATA.ROWCOUNT
    For i = 1 To Table_DATA.ROWCOUNT
        File_line = ""
        For j = 1 To Table_FIELDS.ROWCOUNT
                Start = Table_FIELDS(j, "OFFSET") + 1
                Lenght = Table_FIELDS(j, "LENGTH")
                If (Output_to = "EXCEL") Then
                    Cells(StartRow + i - 1, j).Value = Trim(Mid(Table_DATA(i, "WA"), Start, Lenght))
                Else
                    File_line = File_line & Trim(Mid(Table_DATA(i, "WA"), Start, Lenght)) & File_sep
                End If
        Next j
        If (Output_to = "FILE") Then
            Print #1, File_line
        End If
    Next i
    StartRow = StartRow + Nb_enreg
    'MsgBox "Pass N° " & pass & ". " & Nb_enreg & " record(s) found"
Wend

' Déconnexion
SAP_Func.Connection.logoff

'Mise en forme globale
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
'MsgBox Table_DATA.ROWCOUNT, vbOKOnly, "Nbr d'enregistrements"

If (Output_to = "FILE") Then
    Close #1
End If

End Sub


[Gothmog]

Do you mean the result of a transaction or the technical data of a transaction (as in, program/dynpro called)?

[BonVivant]

Hi,

There was a typo in my original post.

The VBA code works perfectly for TRANSPARENT table. Example; ZIXIIL2_T03,LFA1
However it does not work for "STRUCT" table. Example: CCR1S - S_ALR_87XXXXX

I would like to "modify" the code extract data from "STRUCT" table.

Thanx so much....

Michel
Ottawa.

[Gothmog]

Oh, I see !
Well, there's a good reason for that : STRUCT "tables" are not tables, but only structure definitions. They hold no data.
You'll have to find the tables holding the data.
Here's a useful link for this.

[BonVivant]

Hi Gothmog,

Yes I came across that article.

However it seems my SAP account does not allow for SE11 transactions.

So... it seems I'm still lost in the forest.. but can't see the trees

Thanx for providing the info.

BV

[timesteva]

Hi all!

Previous posts date from a while, but i thought i could still give it a try...
I'm looking as well for some code to extract data from SAP to Excel
I looked at your code, and i have one major question (probably obvious if you are familiar with coding, VBA and SAP , but i'm a total newbie...)
Where/When do you define the data you want to retreive from SAP, i.e. how do you tell the program that you want to retreive such or such information and not the whola database?

I tried your code in a new excel file (could'nt download your excel file...) and it returns an error here

L57

Code: Select all

TABNAME.Value = Sheets("OPTIONS").Range("B1").Value

"execution error '9' "
"The index doesn't belong to the selection"

The text file "rfc_read_table.txt" is empty so i think the error comes from there, and might be related to my first question: where is the data?

Thanks for your help