Excel VBA - Loop in a string

Hi all,

I am new in VBA and I have searched for solution for this issue I have but without any luck. I am sure there is already somewhere but I am doing something wrong...

I have this 2 lines in SAP script:

session.findById("wnd[0]/usr/lbl[1,6]").setFocus
session.findById("wnd[0]/usr/lbl[1,6]").caretPosition = 0


I need that, instead of number 6 there is a loop so I can have all numbers from 6 to for example 9.

For example:

Dim i as Integer
i=6
Do while i=9
session.findById("wnd[0]/usr/lbl[1, i ]").setFocus
session.findById("wnd[0]/usr/lbl[1, i ]").caretPosition = 0
i = i + 1
loop


(this is just a rough draft so you can get an idea what I need)

Thank you very much for all suggestions! I appreciate any help on this

How about something like this?

Code:

---

i=6
Do until i=9
session.findById("wnd[0]/usr/lbl[1, i ]").setFocus
 session.findById("wnd[0]/usr/lbl[1, i ]").caretPosition = 0
  i = i + 1
  loop

---

What's a SAP, and how do you write scripts for it?

SAP is a mainframe system. I have tried writing code for SAP and have failed because for some odd reason or another Excel will not communicate with Citrix(Basically a remote desktop) which can sometimes house SAP itself. They both seem to use the same type of code for automation as well.

Quote:

---

Originally Posted by greyangel

How about something like this?

Code:

---

i=6
Do until i=9
session.findById("wnd[0]/usr/lbl[1, i ]").setFocus
 session.findById("wnd[0]/usr/lbl[1, i ]").caretPosition = 0
  i = i + 1
  loop

---

---

This is not working since [1,6] is not a cell reference in Excel, but a text from script. I just need a loop that will write other numbers instead of 6 in this text. Basically it should concatenate with & or + but I just dont know how to do it...

SAP is and ERP that lot of companies use. It is possible to record macro in SAP same way you can record it in Excel. That is a way I got this script.

You need to do something like this to set the object. I simply just ran the macro recorded within SAP to get the code below. If you can get this to work please let me know because I have been struggling with trying to write code that allows excel to talk to SAP. Does your SAP open via citrix as well?

Code:

---

If Not IsObject(application) Then
  Set SapGuiAuto  = GetObject("SAPGUI")
  Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
  Set connection = application.Children(0)
End If
If Not IsObject(session) Then
  Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
  WScript.ConnectObject session,    "on"
  WScript.ConnectObject application, "on"
End If

---

https://archive.sap.com/discussions/thread/1935037
Also you could look at this forum here for helpful answers as well.

Quote:

---

Originally Posted by greyangel

You need to do something like this to set the object. I simply just ran the macro recorded within SAP to get the code below. If you can get this to work please let me know because I have been struggling with trying to write code that allows excel to talk to SAP. Does your SAP open via citrix as well?

Code:

---

If Not IsObject(application) Then
  Set SapGuiAuto  = GetObject("SAPGUI")
  Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
  Set connection = application.Children(0)
End If
If Not IsObject(session) Then
  Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
  WScript.ConnectObject session,    "on"
  WScript.ConnectObject application, "on"
End If

---

---

Try just with replacing all where you have "application" with "app" only. My script is working like that. I have only issue with this lines since I want to create a loop that will pull other material numbers for example, from my excel list.

found a solution for this integer loop inside string...

Dim Counter As String
Dim i As Integer
Dim wnd As String
Dim endd As String


For i = 6 To 8


wnd = "wnd[0]/usr/lbl[1,"
endd = "]"


Counter = wnd & i & endd




session.findById(Counter).SetFocus
session.findById(Counter).caretPosition = 0
session.findById("wnd[0]").sendVKey 2


Next i

thank you for your effort

Quote:

---

Originally Posted by zotja

found a solution for this integer loop inside string...

Dim Counter As String
Dim i As Integer
Dim wnd As String
Dim endd As String


For i = 6 To 8


wnd = "wnd[0]/usr/lbl[1,"
endd = "]"


Counter = wnd & i & endd




session.findById(Counter).SetFocus
session.findById(Counter).caretPosition = 0
session.findById("wnd[0]").sendVKey 2


Next i

thank you for your effort

---

you can simplify it to:

Code:

---

Dim Counter As String
Dim i As Integer

For i = 6 To 8
    Counter = "wnd[0]/usr/lbl[1," & i & "]"
    session.findById(Counter).SetFocus
    session.findById(Counter).caretPosition = 0
    session.findById("wnd[0]").sendVKey 2
Next i

---

And this should also work:

Code:

---

With session
    For i = 6 To 8
        With .findById("wnd[0]/usr/lbl[1," & i & "]")
            .SetFocus
            .caretPosition = 0
        End With
        .findById("wnd[0]").sendVKey 2
    Next i
End With

---

Quote:

---

Originally Posted by RICVB

you can simplify it to:

Code:

---

Dim Counter As String
Dim i As Integer

For i = 6 To 8
    Counter = "wnd[0]/usr/lbl[1," & i & "]"
    session.findById(Counter).SetFocus
    session.findById(Counter).caretPosition = 0
    session.findById("wnd[0]").sendVKey 2
Next i

---

And this should also work:

Code:

---

With session
    For i = 6 To 8
        With .findById("wnd[0]/usr/lbl[1," & i & "]")
            .SetFocus
            .caretPosition = 0
        End With
        .findById("wnd[0]").sendVKey 2
    Next i
End With

---

---

Great, thanks. I was actually looking for something like this. Thank you very much :thumb

Quote:

---

Originally Posted by zotja

Try just with replacing all where you have "application" with "app" only. My script is working like that. I have only issue with this lines since I want to create a loop that will pull other material numbers for example, from my excel list.

---

So I edited the code below and got an error of "Invalid syntax" at the "Set sapGUIauto= Getobject("SAPGUI")

Code:

---

If Not IsObject(App) Then
  Set SapGuiAuto = GetObject("SAPGUI")
  Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
  Set Connection = App.Children(0)
End If
If Not IsObject(session) Then
  Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
  WScript.ConnectObject session, "on"
  WScript.ConnectObject App, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "mm03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").Text = "2184"
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").caretPosition = 4
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[2]").sendVKey 0
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").Text = "2288"
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").caretPosition = 4
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[2]").sendVKey 0
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").Text = "2139"
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").caretPosition = 4
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP24/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2800/subSUB2:SAPLMGD1:2802/txtMBEW-VERPR").SetFocus
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP24/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2800/subSUB2:SAPLMGD1:2802/txtMBEW-VERPR").caretPosition = 8
End Sub

---

Quote:

---

Originally Posted by greyangel

So I edited the code below and got an error of "Invalid syntax" at the "Set sapGUIauto= Getobject("SAPGUI")

Code:

---

If Not IsObject(App) Then
  Set SapGuiAuto = GetObject("SAPGUI")
  Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
  Set Connection = App.Children(0)
End If
If Not IsObject(session) Then
  Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
  WScript.ConnectObject session, "on"
  WScript.ConnectObject App, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "mm03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").Text = "2184"
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").caretPosition = 4
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[2]").sendVKey 0
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").Text = "2288"
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").caretPosition = 4
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[2]").sendVKey 0
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").Text = "2139"
session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").caretPosition = 4
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP24/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2800/subSUB2:SAPLMGD1:2802/txtMBEW-VERPR").SetFocus
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP24/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2800/subSUB2:SAPLMGD1:2802/txtMBEW-VERPR").caretPosition = 8
End Sub

---

---

My script is same as you have. Only thing that I can think off is that you need to turn on SAP while running this script so it can run in background.

I think I can't run it because of the security settings at my company. I am glad you were able to get your code to work. :)

Thanks for the VBA script. Works like a charm with my accounting macro!
___________________________________
Marius