Excel RFC Data Transfer

[Former Member]

Hi Guys,

<br><br>

First off, my apologies if this thread is in the wrong forum; it seemed to be the most relevant one to my question.

<br><br>

Background<br>

I have written an ABAP program to gather some statistics and input them into a table, this all works well. The problem came when I needed to display this data in a graph. I found that SAP Business graphics was too limited in that it only had a 16 colour palette and a maximum of 32 unique records could be displayed. BEx was discounted after this as it only operates within the BW systems and my data is not held there, which leaves me with Excel.

<br><br>

Problem<br>

I need to make an RFC to the statistics table in the SAP system. Now as far as I can tell my function call is successful (none of my error catches are triggered). The issue is I donu2019t quite know how to actually extract the data from my tables, nor can I find a complete methods list online.

<br><br>

Question<br>

Is my extraction method correct and if so how do I output the results to a spreadsheet?

<br><br>

Code (Some items renamed for anonymity)<br>

Sub GetTable()

<br><br>

'Logon<br>

Dim sapConn As Object 'Declare variant<br>

Dim objRfcFunc As Object<br>

Dim objQueryTab, objRowCount As Object<br>

Dim objOptTab, objFldTab, objDatTab As Object

<br><br>

Set sapConn = CreateObject("SAP.Functions") 'Create ActiveX object<br>

sapConn.LogLevel = 9<br>

sapConn.LogFileName = "C:\sap_vb.txt"

<br><br>

If sapConn.Connection.Logon(0, False) <> True Then 'Try Logon<br>

MsgBox "Cannot Logon to SAP"<br>

End If<br>

'Define function

<br><br>

Set objRfcFunc = sapConn.Add("RFC_READ_TABLE")<br>

Set objQueryTab = objRfcFunc.Exports("QUERY_TABLE")<br>

Set objOptTab = objRfcFunc.Tables("OPTIONS")<br>

Set objFldTab = objRfcFunc.Tables("FIELDS")<br>

Set objDatTab = objRfcFunc.Tables("DATA")

<br><br>

objQueryTab.Value = "TABLE_NAME"

<br><br>

'First we set the condition<br>

'Refresh table<br>

objOptTab.FreeTable<br>

'Then set values<br>

objOptTab.Rows.Add<br>

objOptTab(objOptTab.ROWCOUNT, "TEXT") = "WEEKDATE = '" + Format((Date + 6 - Weekday(Date)), "yyyymmdd") + "'"

<br><br>

'Next we set fields to obtain<br>

'Refresh table<br>

objFldTab.FreeTable<br>

'Then set values<br>

objFldTab.Rows.Add<br>

objFldTab(objFldTab.ROWCOUNT, "FIELDNAME") = "USERNAME"<br>

objFldTab.Rows.Add<br>

objFldTab(objFldTab.ROWCOUNT, "FIELDNAME") = "SOPTOTAL"<br>

objFldTab.Rows.Add<br>

objFldTab(objFldTab.ROWCOUNT, "FIELDNAME") = "SOTTOTAL"<br>

objFldTab.Rows.Add<br>

objFldTab(objFldTab.ROWCOUNT, "FIELDNAME") = "INCTOTAL"<br>

objFldTab.Rows.Add<br>

objFldTab(objFldTab.ROWCOUNT, "FIELDNAME") = "OLDTOTAL"

<br><br>

If objRfcFunc.Call = False Then<br>

MsgBox objRfcFunc.Exception<br>

End If

<br><br>

u2018Up to this point there are no issues below is some rather poor display code

<br><br>

'Dim intRow, intField As Integer<br>

'intRow = objDatTab.Rows

<br><br>

Range("A1").Select<br>

ActiveCell.Value = CStr(objDatTab("WEEKDATE"))<br>

'For intRow = 1 To objDatTab.Rows<br>

' For intField = 1 To objFldTab.Rows<br>

' Cells(intRow, intField) = Mid(objDatRec("WA"), objFldRec("OFFSET") + 1, objFldRec("LENGTH"))<br>

' Next<br>

'Next

<br><br>

End Sub

<br><br>

Edited by: UKIntern on May 20, 2009 11:03 AM (Formatting issues)

[Former Member]

Ill just answer my own question:

Dim index As Integer

index = 1

Range("A2").Select

Do While index <= objDatTab.ROWCOUNT

'Populate Contents into Excel Sheet

ActiveCell.Offset(index, 0).Value = objDatTab(index, "COLUMN NAME")

index = index + 1

Loop