Continuous processing until blank

[Former Member]

Hi again guys/ ScriptMan,

I have a SAP recording which is repetitive as follows:

session.findById("wnd[0]/tbar[0]/okcd").text = "mmrv"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtMARV-BUKRS").text = "A111"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/chkMARV-XRUEM").selected = false

session.findById("wnd[0]/usr/chkMARV-XRUEM").setFocus

session.findById("wnd[0]/tbar[0]/btn[11]").press

session.findById("wnd[0]/usr/ctxtMARV-BUKRS").text = "B111"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/chkMARV-XRUEM").selected = false

session.findById("wnd[0]/usr/chkMARV-XRUEM").setFocus

session.findById("wnd[0]/tbar[0]/btn[11]").press

session.findById("wnd[0]/usr/ctxtMARV-BUKRS").text = "C111"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/chkMARV-XRUEM").selected = false

session.findById("wnd[0]/usr/chkMARV-XRUEM").setFocus

session.findById("wnd[0]/tbar[0]/btn[11]").press

As you have notice, only A111, B111, C111 keeps changing. In excel they are arranged in a single column row by row. Can u help me make a condition to loop the process until it reaches blank? It would be so much helpful.

Thanks in advance.

[thomas_brutigam2 · Accepted solution]

Hi Kevin,

You can do as following:

I hereby assume that the Column is "A"-this you have to change for your needs...

For i = 1 To Range("Tabelle1!A:A").Count
        If Range("A" & CStr(i)).Value <> "" Then
            session.FindById("wnd[0]/usr/ctxtMARV-BUKRS").Text = Range("Tabelle1!A" & CStr(i)).Value
            session.FindById("wnd[0]").SendVKey 0
            session.FindById("wnd[0]/usr/chkMARV-XRUEM").Selected = False
            session.FindById("wnd[0]/usr/chkMARV-XRUEM").SetFocus
            session.FindById("wnd[0]/tbar[0]/btn[11]").press
        Else
            Exit For
        End If
    Next i

Greetings

[Former Member · Accepted solution]

Hi Thomas,

Thank you very much for the reply. I think it's almost working but i'm just getting some errors. I simplified the transaction codes as per below:

For i = 1 To Range("A:A").Count

        If Range("A" & CStr(i)).Value <> "" Then

            session.findById("wnd[0]/usr/ctxtMARV-BUKRS").Text = Range("A:A" & CStr(i)).Value

            session.findById("wnd[0]").SendVKey 0

            session.findById("wnd[0]/tbar[0]/btn[3]").press

        Else

            Exit For

        End If

    Next i

1. I removed "Tabelle1!" as it is making some errors (range of object global failed)

2. After removing that, it made the same error on the first session.findBy line.

3. I am testing my data with just three inputs in cells A1:A3, worksheet name is "Check MMRV"

Hoping for your kind help.

[thomas_brutigam2 · Accepted solution]

Hi Kevin,

got me a "copy Paste Error"

Try this way ---

For i = 1 to 10

If Range("A" & CStr(i)).Value <> "" Then

  session.findById("wnd[0]/usr/ctxtMARV-BUKRS").Text = Range("A" & CStr(i)).Value

            session.findById("wnd[0]").SendVKey 0

            session.findById("wnd[0]/tbar[0]/btn[3]").press

     else

     exit for

end if

next i

[Former Member · Accepted solution]

Hi Thomas,

huhuhu. Still getting error on this part:

session.findById("wnd[0]/usr/ctxtMARV-BUKRS").Text = Range("A" & CStr(i)).Value

The control could not be found by id

And i added the transaction code line:

For i = 1 To 10

If Range("A" & CStr(i)).Value <> "" Then

        session.findById("wnd[0]/usr/ctxtMARV-BUKRS").Text = Range("A" & CStr(i)).Value

        session.findById("wnd[0]/tbar[0]/okcd").Text = "mmrv"

        session.findById("wnd[0]").SendVKey 0

        session.findById("wnd[0]/tbar[0]/btn[3]").press

     Else

     Exit For

End If

Next i

[thomas_brutigam2 · Accepted solution]

Hi Kevin,

Should the Transaction be called only once or always with any Element ??

In any case-

allright, here we go again:

session.findById("wnd[0]/tbar[0]/okcd").text = "mmrv"

session.findbyid("wnd[0]").sendvkey 0

For i = 1 to 10

     If Range("A" & CSTr(i)).Value <> "" then

           session.findbyId("wnd[0]/usr/ctxtMARV-BUKRS").Text = Range("A" & CStr(i)).Value

               session.findById("wnd[0]").sendVKey 0

               session.findById("wnd[0]/usr/chkMARV-XRUEM").selected = false

               session.findById("wnd[0]/usr/chkMARV-XRUEM").setFocus

               session.findById("wnd[0]/tbar[0]/btn[11]").press

     else

          exit for

     end if

next i

That is what you wrote in your first posting...

By the way - are you scripting via vbs or in Excel VBA?

[Former Member · Accepted solution]

Hi Thomas,

Very much glad for the help. It finally worked! Yey!!!

I am using Excel macro to execute the SAP transactions. I took the codes through recording. But here's my final code:

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmmrv"

session.findById("wnd[0]").sendVKey 0

For i = 1 To 10

If Range("A" & CStr(i)).Value <> "" Then

        session.findById("wnd[0]/usr/ctxtMARV-BUKRS").Text = Range("A" & CStr(i)).Value

        session.findById("wnd[0]").sendVKey 0

        Application.Wait (Now + TimeValue("0:00:03"))

        session.findById("wnd[0]/tbar[0]/btn[3]").press

     Else

     Exit For

End If

Next i

My aim is to execute MMRV and take a look at each company in my list in Excel column A. I added the 3-second delay to let me have a glimpse of the result. Then exits and get the next company code and loops until the list if finished.

Oh... i just realized "For i = 1 To 10"... i can just set this to 1000 or more to make sure all is captured.

Your code really helps. Thank you very much

[thomas_brutigam2 · Accepted solution]

Hi Kevin,

Thats fine...

Like you wrote first:
if you want to go through the whole A Column until Empty:

Have a look at this:

FAQ 0105: Wie ermittle ich die letzte benutzte Zeile/Spalte in einem Excel-Sheet? - ActiveVB

[holger_khn · Accepted solution]

Hello.

As your code got executed from Excel macro there is no need for application.wait.

Next code line just got executed when previous one is done.

SAP send back an Event to Excel when this happen.

One of the reason why we can´t handle MS SaveAs or Open dialogue from same Excel instance macros.

[Former Member · Accepted solution]

Hi Thomas,

Thanks again. I have a lot of it to apply to, really a big help. And i bookmarked the new link you gave . I have used xlCellTypeLastCell before you replied, but i guess it needs to set xclapp as reference (which i am trying to avoid to make it usable for everyone).

Thanks again!

[Former Member · Accepted solution]

Hi Holger,

Thanks for the open dialogue info. What i actually plan on doing is to check the result of the transaction (t-code FS10N) manually. I'm just gonna have a peek... as i may say... if there is a balance for each of the items in my list. Then i can just list those items on a paper (my 3-second window) and just go back to it once done processing everything.

[Former Member]

can anyone help me with this?