Excel VBA - Loop in a string

[zotja]

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

[greyangel]

How about something like this?


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

[SamT]

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

[greyangel]

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.

[zotja]

How about something like this?


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.

[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?


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

[greyangel]

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

[zotja]

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?


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.

[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

[RICVB]

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:


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:



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

[zotja]

you can simplify it to:


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:



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

[greyangel]

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")

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

[zotja]

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

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.

[greyangel]

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. :)

[Marius Titul]

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