RFC_READ_TABLE where IN - VBA

[Former Member]

Hi guys.

I'm using RFC_READ_TABLE transaction to parse some data from a table in SAP.

The problem is that I want to get the data for more than one order therefore I use the IN in the query as bellow:

tbloptions.AppendRow

tbloptions(1, "TEXT") = Chr(34) & "WERKS EQ '0012' AND AUFNR IN" & "(" & ordCollector & ")" & Chr(34)

ordCollector is a string variable where I add the order numbers respecting the syntax.

    Dim Rand As Long

    Dim LRand As Long

    LRand = ws.Cells(Rows.Count, 1).End(xlUp).Row

    Rand = 2

    If LRand = 2 Then

        ordCollector = "'" & ws.Cells(Rand, 1).Value & "'"

    Else

        Do While ws.Cells(Rand, 1).Value <> "" And Rand < LRand

            ordCollector = ordCollector + "'" & ws.Cells(Rand, 1).Value & "', "

            Rand = Rand + 1

        Loop

        ordCollector = ordCollector + "'" & ws.Cells(Rand, 1).Value & "'"

    End If

with these two pieces of code I wanted to have something like:

     tbloptions(1, "TEXT") = "WERKS EQ '0012' AND AUFNR IN ('1234567', '8910111', '2131415')"

I don't know if it' something wrong with the syntax I'm using (if I use chr(24) the workbook stops responding and if I'm not the RFC_READ_TABLE gives me an error.

Any ideas? If you want more details please ask.

Thank you!

[Former Member]

And if there are some better ways to do this stuff in VBA I'm open to your sugestions!

Thanks!

[Former Member]

Hi Andrei,

nice code, but use for loop already count no of rows.

LRand = ws.Cells(Rows.Count, 1).End(xlUp).Row

ordCollector = ""

for Rand = 2 to LRand

     if trim(ws.Cells(Rand, 1).Value)<>"" then

          ordCollector = ordCollector  &  "'" & ws.Cells(Rand, 1).Value & "', "

     end if

next

ordCollector = left(ordCollector , len(ordCollector ) -1 )

Try this

All the best.

Regards,

Viswanathan S

[Former Member]

Thanks for the tip. The problem is that this piece of code doesn't work. I would appreciate a method to use rfc_read_table for multiple values... (ie. multiple orders in this case). I have the orders in a range and I want to check the material movements on all of them. Any ideas? Thanks guys!

[Former Member]

Hi Andrei,

can you tell which part code not work. i can explain. multi order or single order its store in ordCollector .

starting row is 2 upto rand to range update in ordCollector.

pls check it and reply.

Regards

Viswanathan S

[Former Member]

Hi,

This is the part that I believe it doesn't work

tbloptions(1, "TEXT") = Chr(34) & "WERKS EQ '0012' AND AUFNR IN" & "(" & ordCollector & ")" & Chr(34)

I wanted the result of the above code to be something like:

tbloptions(1, "TEXT") = "WERKS EQ '0012' AND AUFNR IN ('1234567', '8910111', '2131415')"

The above piece of code works but when I'm trying it with ordCollector instead of actual order numbers it doesn't work.

In ordCollector I could have even 20 orders... could it be because of it? Are there any limitations to RFC_READ_TABLE? I'm askind this because I know that when I wanted to add multiple coditions I have to do it on more than one row.

[Former Member]

hi Andrei,

Try this code.

Dim Rand As Long

Dim LRand As Long

LRand = ws.Cells(Rows.Count, 1).End(xlUp).Row

        ordCollector = ""

          for Rand = 2 to LRand

               if trim(ws.Cells(Rand, 1).Value)<>"" then

                    ordCollector = ordCollector  &  "'" & ws.Cells(Rand, 1).Value & "', "

               end if

          next

          ordCollector = left(ordCollector , len(ordCollector ) -1 )

          tbloptions.AppendRow

          tbloptions(1, "TEXT") = "WERKS EQ '0012' AND AUFNR IN (" & ordCollector & ")"

Regards

Viswanathan S

[Former Member]

Any ideas? Thanks!

[Former Member]

Problem solved. Instead of IN I'll use OR for tbloptions. It works great!

[former_member615784]

Hi Andrei,

Actually Im facing the same trouble!

You understand me man, was a long searching on the web to finally found this,

Unfortunately  for me the same error keeps poping up, "System Failure",

Could you please share your solution=?, Im using table MARD, with several material numbers,

I tried:

tbloptions(1, "TEXT") = "WERKS EQ '7034' AND MATNR OR (" & ordCollector & ")"

Is this how you did it?

Please help!!

[Former Member]

Hello,

I've got the same problem, please explain your solution in more details.

Thank you,

Csaba

[robson_soares]

I think instead of:

WERKS EQ '0012' AND AUFNR IN ('1234567', '8910111', '2131415')

he used:

WERKS EQ '0012' AND (

                      AUFNR EQ '1234567' OR

                      AUFNR EQ '8910111' OR

                      AUFNR EQ '2131415'

                    )

[Former Member]

Thank you, but how to be used in case of an array?

Exact description of my problem is here:

Thanks,

Csaba

[Former Member]

You have to take a close look at the length of each options statement - when these data is transfered to the SAP functionmodule it happens i text lines of 72 characters each! This meens that you have to think in "line mode" when you build up your options, and one more thing - it is a good idea to start each line with a blank character (try to imagine how the final SQL-statement will look like, when all ljnes are put together in the end).

Regards Dan