04-15-2008 1:09 PM
Hi All,
I am using the following VBA code to call table information from SAP.
Sub GetTable()
'Logon
Dim sapConn As Object 'Declare variant
Set sapConn = CreateObject("SAP.Functions") 'Create ActiveX object
If sapConn.Connection.Logon(0, False) <> True Then 'Try Logon
MsgBox "Cannot Log on to SAP"
End If
'Define function
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 = "100"
Dim objOptTab, objFldTab, objDatTab As Object
Set objOptTab = objRfcFunc.Tables("OPTIONS")
Set objFldTab = objRfcFunc.Tables("FIELDS")
Set objDatTab = objRfcFunc.Tables("DATA")
'First we set the condition
'Refresh table
objOptTab.FreeTable
'Next we set fields to obtain
'Refresh table
objFldTab.FreeTable
'Then set values
objFldTab.Rows.Add
objFldTab(objFldTab.RowCount, "FIELDNAME") = "MANDT"
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
This code will download all the information in the table but I want to be able to filter the information I am calling.
Does anyone know how I could set parameters in the code (e.g. only rows would be downloaded when fieldname MANDT = 900)?
Thanks
04-15-2008 1:25 PM
objOptTab.Rows.Add
objOptTab(objOptTab.RowCount, "TEXT") = "MANDT = '900'"
best regards,darek
04-15-2008 1:25 PM
objOptTab.Rows.Add
objOptTab(objOptTab.RowCount, "TEXT") = "MANDT = '900'"
best regards,darek
04-15-2008 3:07 PM
Thanks Darek,
Do you know how I would alter this to do a 'between' statement.
objOptTab.Rows.Add
objOptTab(objOptTab.RowCount, "TEXT") = "LGTYP = 'H01'"
I want this to state LGTYP is between H01 and K06
Thanks,
Mike
04-17-2008 8:56 AM
hi,
try this:
objOptTab(objOptTab.RowCount, "TEXT") = "LGTYP BETWEEN 'H01' AND 'HZZ"
best regards,darek
04-17-2008 9:31 AM
Hi,
The code doesn't seem to work, I keep getting an exception message saying SYSTEM_FAILURE.
Mike
04-17-2008 9:49 AM
sorry, a little mistake in vba code,
should be:
....="LGTYP BETWEEN 'H01' AND 'K06'"
you may test your conditions
directly in se37 for function module
RFC_READ_TABLE ...
darek
04-17-2008 10:05 AM
Thanks Darek,
The code now works. Do you know how to set the code to return the column label headings that are in SAP for the columns I specify?
Mike
04-17-2008 11:04 AM
hi Mike,
labels are available in your VBA 5 column of table objFldTab after calling rfc function, example:
first field label (MANDT):
MsgBox objFldTab(1,5)
third field label (MATNR):
MsgBox objFldTab(3,5)
darek...
reward points if helpful
04-17-2008 11:16 AM
Hi,
I have managed to get the column headings in but then this part of the code overwrites them.
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
I need it to start from Row 2 and then keep going down
Mike
04-18-2008 9:53 AM
03-09-2022 3:58 AM
data returned as text with delimiter in rows heading “WA”
Use VBA ”Split” command to target specific columns of output.
For n = 1 to rows on object table returned from call.
array(n) = split(func.tables(“DATA”).columns(“WA”).value(n) , delim)