call RFC_READ_TABLE via VBA.

[Former Member]

I try to get data from table LFBK (bank data for vendor).

Here is my VBA code to get data, it's not works. Anyone can suggest me what's wrong? Thank you very much.

Anek

'**************************************

Public Sub RFC_Read_Table()

Dim Functions As Object

Dim RfcCallTransaction As Object

Dim Messages As Object

Dim BdcTable As Object

Dim tblOptions

Dim tblData

Dim tblFields

Dim strExport1

Dim strExport2

Dim filOutput

Dim intRow As Integer

Set Functions = CreateObject("SAP.Functions")

Functions.Connection.System = "PDS"

Functions.Connection.client = "240"

Functions.Connection.user = "S0110028"

Functions.Connection.Password = "0603cacc"

Functions.Connection.Language = "EN"

If Functions.Connection.Logon(0, False) <> True Then

Exit Sub

End If

Set RfcCallTransaction = Functions.Add("RFC_READ_TABLE")

Set strExport1 = RfcCallTransaction.exports("QUERY_TABLE")

Set strExport2 = RfcCallTransaction.exports("DELIMITER")

Set tblOptions = RfcCallTransaction.Tables("OPTIONS") '

Set tblData = RfcCallTransaction.Tables("DATA") '

Set tblFields = RfcCallTransaction.Tables("FIELDS") '

strExport1.Value = "LFBK"

strExport2.Value = ";"

tblOptions.AppendRow

tblOptions(1, "TEXT") = "LIFNR EQ '41'" ' Suppose I want to bank data from vendor number 41.

tblFields.AppendRow

tblFields(1, "FIELDNAME") = "LIFNR"

tblFields.AppendRow

tblFields(2, "FIELDNAME") = "BANKS"

tblFields.AppendRow

tblFields(3, "FIELDNAME") = "BANKL"

tblFields.AppendRow

tblFields(4, "FIELDNAME") = "BANKN"

tblFields.AppendRow

tblFields(5, "FIELDNAME") = "BUTYP"

If RfcCallTransaction.Call = True Then

If tblData.RowCount > 0 Then

Dim DatTxt, OutputTxt As String

OutputTxt = "Vendor, Bank Country, Bank Key, Book No., Run No."

DatTxt = "c:\bankdata.txt"

Open DatTxt For Output As #1

Print #1, OutputTxt

For intRow = 1 To tblData.RowCount

OutputTxt = tblData(intRow, "WA")

Print #1, OutputTxt

Next

Close #1

Else

MsgBox "No records returned"

End If

Else

MsgBox "Error"

End If

Set filOutput = Nothing

Functions.Connection.Logoff

End Sub

[athavanraja]

is it not connecting to the system or the problem is with executing the FM

Raja

[Former Member]

connecting to system is work fine. but when debugging, i saw some error likes "bad index". you can copy this code

to testing in your ms. excel.

thanks for your reply.

anek

[athavanraja]

you are passing 41 to LIFNR.

the internal format of LIFNR is 10 characters long. so you have to prefix 41 with 8 zeros.

instead of 41 pass '0000000041' to LIFNR

Regards

Raja

[Former Member]

Raja,

thanks very much, now it's works.

Regards,

anek

[Former Member]

if I want to put more than one vendor number, for example '0000000041' and '0000000060'. how do I have to changes my codes? Thank your very much.

Anek

tblOptions.AppendRow

tblOptions(1, "TEXT") = "LIFNR EQ '0000000041'"

[athavanraja]

tblOptions.AppendRow

tblOptions(1, "TEXT") = "LIFNR IN ('0000000041','0000000060')"

Regards

Raja

[former_member615784]

Hi Raj,

I've looking a long time on the web, i would like some direction on this,

Im facing excatly the same trouble above, just a little different,  i need to place several numbers on the list, when i type more than 2  number on: tblOptions(1, "TEXT") = "LIFNR IN ('0000000041','0000000060')"   , it fires an error "system failure".

I need to pull out about 1k data numbers and i wrote a piece of code to add several numbers in a string variable:

Dim material as String

dim myMaterialNumber(10000) as String

i = 1
j = 0
Do While Worksheets("Sheet1").Range("M2").Cells(i, 1) <> ""
    material = Worksheets("Sheet1").Range("M2").Cells(i, 1)
    j = j + 1
    If Len(material) = 10 Then
        myMaterialNumber(j) = "00000000" & Replace(material, "-", "")
    Else
        myMaterialNumber(j) = "00000" & Replace(material, "-", "")
    End If
   
     If j = 1 Then
        eljuntos = "'" & myMaterialNumber(j)

     Else

        eljuntos = eljuntos & "','" & myMaterialNumber(j)
     End If
i = i + 1
Loop

eljuntos = eljuntos & "'"

But when i try to use the sentence IN(, it appears a "system failure error", and doing it one by one takes too long.

objOptTab.Rows.Add

objOptTab(objOptTab.RowCount, "TEXT") = "MATNR IN (" & eljuntos & ") and "

objOptTab.Rows.Add

objOptTab(objOptTab.RowCount, "TEXT") = "WERKS EQ '7034'"

This passes with no error,

But when the function is called:

If objRfcFunc.call = False Then

      MsgBox objRfcFunc.Exception

End If

It fires a "System Failure" Error.

I've found that the sentence OR could be helpful but i haven't found how this sentece works.

Please help Raj!

Many thanks in advance!

[Former Member]

Hello Raj,

   I too working on same but i am automating the report with date but no success i tried with sy-datum its fine but i want it with sy-datum -1 , for previous day data to be fetch from sap. Please help

Regards

Chetan S.