SET PARAMETERS WHEN CALLING RFC FROM VBA

[Former Member]

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

[Former Member]

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

[Former Member]

objOptTab.Rows.Add

objOptTab(objOptTab.RowCount, "TEXT") = "MANDT = '900'"

best regards,darek

[Former Member]

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

[Former Member]

hi,

try this:

objOptTab(objOptTab.RowCount, "TEXT") = "LGTYP BETWEEN 'H01' AND 'HZZ"

best regards,darek

[Former Member]

Hi,

The code doesn't seem to work, I keep getting an exception message saying SYSTEM_FAILURE.

Mike

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

Need last question resolving before this is answered

[former_member793811]

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)