on 12-19-2011 9:11 AM
Hi Guys,
I am trying to use this RFC to get a dump of any specified SAP table to Excel. So far with a help for another example I have come up with this:
-
Sub Get_table()
Dim R3, MyFunc, App As Object
Dim ENTRIES, ROW As Object
Dim Result As Boolean
Dim iRow, iColumn, iStart, iStartRow As Integer
iStartRow = 4
Worksheets(1).Select
Cells.Clear
'**********************************************
'Create Server object and Setup the connection
'**********************************************
Set R3 = CreateObject("SAP.Functions")
R3.Connection.System = "SEQ"
R3.Connection.client = "200"
R3.Connection.language = "EN"
If R3.Connection.logon(0, False) <> True Then
Exit Sub
End If
'*****************************************************
'Call RFC_GET_TABLE_ENTRIES
'*****************************************************
Set MyFunc = R3.Add("RFC_GET_TABLE_ENTRIES")
Dim oParam1 As Object
Dim oParam2 As Object
Dim oParam3 As Object
Dim oParam4 As Object
Dim oParam5 As Object
Set oParam1 = MyFunc.exports("TABLE_NAME")
oParam1.Value = frmQuery.txtTableName
' Form contains a field with a table name that a user needs to fill in
Set oParam2 = MyFunc.exports("FROM_KEY")
Set oParam3 = MyFunc.exports("GEN_KEY")
Set oParam4 = MyFunc.exports("BYPASS_BUFFER")
Set oParam5 = MyFunc.exports("TO_KEY")
oParam2.Value = ""
oParam3.Value = ""
oParam4.Value = ""
oParam5.Value = ""
oParam6.Value = ""
Result = MyFunc.CALL
If Result = True Then
Set ENTRIES = MyFunc.Tables("ENTRIES")
Else
MsgBox MyFunc.EXCEPTION
R3.Connection.LOGOFF
Exit Sub
End If
R3.Connection.LOGOFF
If Result <> True Then
MsgBox (EXCEPTION)
Exit Sub
End If
-
This is as far as I got really.
An example I used was using a different RFC call to get the data: (TABLE_ENTRIES_GET_VIA_RFC) and it had additional lines of code for filling in the Excel worksheet with the data. I have tried to modify that code to get it working with the data I get from RFC_GET_TABLE_ENTRIES but failed miserably - VB is not my first or even second language
Could you please help me with this task? I need a bit of code that will put the data into the worksheet
Hello Rafal,
try this:
Option Explicit
Sub Test()
Dim R3, MyFunc, App As Object
Dim ENTRIES As SAPTableFactoryCtrl.Table
Dim Row As SAPTableFactoryCtrl.Row
Dim Result As Boolean
Dim iRow, iColumn, iStart, iStartRow As Integer
Dim i As Long
iStartRow = 4
Worksheets(1).Select
Cells.Clear
'**********************************************
'Create Server object and Setup the connection
'**********************************************
Set R3 = CreateObject("SAP.Functions")
R3.Connection.System = "NSP"
R3.Connection.Client = "000"
R3.Connection.User = "BCUSER"
R3.Connection.Language = "DE"
If R3.Connection.logon(0, False) <> True Then
Exit Sub
End If
'*****************************************************
'Call RFC_GET_TABLE_ENTRIES
'*****************************************************
Set MyFunc = R3.Add("RFC_GET_TABLE_ENTRIES")
Dim oParam1 As Object
Dim oParam2 As Object
Dim oParam3 As Object
Dim oParam4 As Object
Dim oParam5 As Object
Set oParam1 = MyFunc.exports("TABLE_NAME")
oParam1.Value = "CREP_HTTP"
'Form contains a field with a table name that a user needs to fill in
Set oParam2 = MyFunc.exports("FROM_KEY")
Set oParam3 = MyFunc.exports("GEN_KEY")
Set oParam4 = MyFunc.exports("BYPASS_BUFFER")
Set oParam5 = MyFunc.exports("TO_KEY")
oParam2.Value = ""
oParam3.Value = ""
oParam4.Value = ""
oParam5.Value = ""
Result = MyFunc.CALL
If Result = True Then
Set ENTRIES = MyFunc.Tables("ENTRIES")
Else
MsgBox MyFunc.EXCEPTION
R3.Connection.LOGOFF
Exit Sub
End If
R3.Connection.LOGOFF
'>Fills the Excel sheet<
For i = 1 To ENTRIES.RowCount
Set Row = ENTRIES.Rows(i)
Cells(i + iStartRow, 1) = Row.Value("WA")
Next
If Result <> True Then
MsgBox (MyFunc.EXCEPTION)
Exit Sub
End If
End Sub
Hope it helps.
Cheers
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Stefan,
Thanks for your help. I had to change the code a little bit as not everything was working. The whole thing looks like this now:
-
Sub Get_table()
Dim R3, MyFunc, App As Object
Dim ENTRIES As Object
Dim Row As Object
Dim Result As Boolean
Dim iRow, iColumn, iStart, iStartRow As Integer
Dim i As Long
iStartRow = 4
Worksheets(1).Select
Cells.Clear
'**********************************************
'Create Server object and Setup the connection
'**********************************************
Set R3 = CreateObject("SAP.Functions")
R3.Connection.System = "System"
R3.Connection.client = "200"
R3.Connection.user = "user"
R3.Connection.password = ""
R3.Connection.language = "EN"
If R3.Connection.logon(0, False) <> True Then
Exit Sub
End If
'*****************************************************
'Call RFC function TABLE_ENTRIES_GET_VIA_RFC
'*****************************************************
Set MyFunc = R3.Add("RFC_GET_TABLE_ENTRIES")
Dim oParam1 As Object
'Dim oParam2 As Object
Dim oParam3 As Object
Dim oParam4 As Object
Dim oParam5 As Object
Dim oParam6 As Object
Set oParam1 = MyFunc.exports("TABLE_NAME")
oParam1.Value = frmQuery.txtTableName
'Set oParam2 = MyFunc.exports("MAX_ENTRIES")
'oParam2.Value = "20"
Set oParam3 = MyFunc.exports("FROM_KEY")
Set oParam4 = MyFunc.exports("GEN_KEY")
Set oParam5 = MyFunc.exports("BYPASS_BUFFER")
Set oParam6 = MyFunc.exports("TO_KEY")
oParam3.Value = ""
oParam4.Value = ""
oParam5.Value = ""
oParam6.Value = ""
Result = MyFunc.CALL
If Result = True Then
Set ENTRIES = MyFunc.Tables("ENTRIES")
Else
MsgBox MyFunc.EXCEPTION
R3.Connection.LOGOFF
Exit Sub
End If
'>Fills the Excel sheet<
For i = 1 To ENTRIES.RowCount
Set Row = ENTRIES.Rows(i)
Cells(i + iStartRow, 1) = Row.Value("WA")
Next
If Result <> True Then
MsgBox (MyFunc.EXCEPTION)
Exit Sub
End If
'*******************************************
'Quit the SAP Application
'*******************************************
R3.Connection.LOGOFF
If Result <> True Then
MsgBox (EXCEPTION)
Exit Sub
End If
End Sub
-
Unfortunately all the data gets loaded into one column.
How do I separate it and write the headers for each column so each data column in Excel is a separate data field and has a header which is a name of the field in SAP?
Sorry to be a pain - I know its basic VB stuff, but like I mentioned I do not have a lot of experience in programming.
Hello Rafal,
you are using RFC_GET_TABLE_ENTRIES and the ENTRIES structure is TAB512. This is one character field with a length of 512 characters. With this function module you get one record of the table in one line. That is the reason you get the record in one field of Excel. If you need each field of the table in different columns of the sheet, you must cut the ENTRIES at the correct position.
Cheers
Stefan
Hello Rafal,
RFC_READ_TABLE also return the records in TAB512.
Here is an example how to get offset and length of a field:
"-Begin-----------------------------------------------------------------
"-
"- Include zGetOffset
"-
"- Procedure to get offset and length of fields from a table
"-
"- Author: Stefan Schnell
"- Version: 1.0
"-
"-----------------------------------------------------------------------
"-Procedure GetOffset-------------------------------------------------
"-
"- Call it: Perform GetOffset Using 'TSTC' 'DYNPRO' Changing ofs len.
"- Parameter: TableName of table you want to know
"- FieldName of the field you want to know
"- Return: Offset of the field, position where the content of
"- the field starts
"- Length of the field
"-
"---------------------------------------------------------------------
Form GetOffset Using TableName Type string
FieldName Type string
Changing Offset Type i
Length Type i.
Data: Begin Of DD03L_Fields Occurs 0,
TABNAME Like DD03L-TABNAME,
FIELDNAME Like DD03L-FIELDNAME,
POSITION Like DD03L-POSITION,
LENG Like DD03L-LENG,
End Of DD03L_Fields.
Data: ofs Type i.
Select DD03L~TABNAME
DD03L~FIELDNAME
DD03L~POSITION
DD03L~LENG
From DD03L
Into Corresponding Fields Of Table DD03L_Fields
Where DD03L~TABNAME = TableName.
Sort DD03L_Fields By Position.
ofs = 0. "or ofs = 1.
Loop At DD03L_Fields.
If DD03L_Fields-FIELDNAME = FieldName.
Exit.
EndIf.
ofs = ofs + DD03L_Fields-LENG.
EndLoop.
Offset = ofs.
Length = DD03L_Fields-LENG.
EndForm.
"-End-------------------------------------------------------------------
Cheers
Stefan
Edited by: Stefan Schnell on Dec 21, 2011 2:28 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.