Skip to Content
0
Former Member
Apr 17, 2008 at 09:44 AM

CALL RFC DATA FROM TWO LINKED TABLES IN EXCEL VBA

375 Views

Hi,

I am using Excel VBA to call information from tables in SAP.

This is working correctly, however I now need to be able to call information from another table where the two tables are linked by a common data field.

Example.

The first table I have lists all items in stock and contains an article number. The second table contains all article numbers and their descriptions.

I want to be able to call the first table but to have the article codes description on there aswell.

Here is the code I am currently using.

Sub GetTable()

'************************************************************
'Connect to SAP
'************************************************************
Dim sapConn As Object 'Declare variant
Set sapConn = CreateObject("SAP.Functions") 'Create ActiveX object

sapConn.Connection.System = "QA2"
sapConn.Connection.client = "900"
sapConn.Connection.user = "mbrough"
sapConn.Connection.Password = "st34lh"
sapConn.Connection.Language = "EN"

If sapConn.Connection.Logon(1, False) <> True Then 'Try Logon
   MsgBox "Cannot Log on to SAP"
End If

'*************************************************************
'Define the table specifics
'*************************************************************
Dim objRfcFunc As Object
Set objRfcFunc = sapConn.Add("RFC_READ_TABLE")

Dim objQueryTab, objRowCount As Object
Set objQueryTab = objRfcFunc.Exports("QUERY_TABLE")
objQueryTab.Value = "LQUA"
Set objRowCount = objRfcFunc.Exports("ROWCOUNT")
objRowCount.Value = "15000"

Dim objOptTab, objFldTab, objDatTab As Object
Set objOptTab = objRfcFunc.Tables("OPTIONS")
Set objFldTab = objRfcFunc.Tables("FIELDS")
Set objDatTab = objRfcFunc.Tables("DATA")

'*************************************************************
'Set the condition and refresh the table
'*************************************************************
objOptTab.FreeTable
objOptTab.Rows.Add
objOptTab(objOptTab.RowCount, "TEXT") = "LGTYP BETWEEN 'K01' AND 'K06'"

'*************************************************************
'Set fields to obtain and refresh table
'*************************************************************
objFldTab.FreeTable

'*************************************************************
'Then set values to call
'*************************************************************
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "LGNUM"
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "MATNR"
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "WERKS"
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "LGTYP"
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "LGPLA"
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "GESME"
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "VERME"
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "MEINS"


If objRfcFunc.Call = False Then
   MsgBox objRfcFunc.Exception
End If

Dim objDatRec As Object
Dim objFldRec As Object
For Each objDatRec In objDatTab.Rows
   For Each objFldRec In objFldTab.Rows
      Cells(objDatRec.Index, objFldRec.Index) = _
            Mid(objDatRec("WA"), objFldRec("OFFSET") + 1, objFldRec("LENGTH"))
   Next
Next

End Sub

The table which contains the article descriptions is called 'MAKT' and this table also contains the column 'MATNR' which is the article field.

Many thanks,

Mike