Skip to Content
avatar image
Former Member

RFC_READ_TABLE where IN - VBA

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Oct 08, 2012 at 01:52 PM

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

    Thanks!

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Oct 08, 2012 at 05:42 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • avatar image
    Former Member
    Oct 09, 2012 at 07:28 AM

    Any ideas? Thanks!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 09, 2012 at 11:13 AM

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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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