Excel VBA - Loop in a string

**zotja** · 10-30-2017, 08:05 AM

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** · 10-30-2017, 08:30 AM

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** · 10-30-2017, 12:04 PM

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

**greyangel** · 10-30-2017, 12:12 PM

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** · 10-30-2017, 12:49 PM

Originally Posted by 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

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** · 10-30-2017, 01:01 PM

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** · 10-30-2017, 01:35 PM

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

**zotja** · 10-30-2017, 02:13 PM

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?

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** · 10-30-2017, 02:37 PM

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** · 10-30-2017, 03:05 PM

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:

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** · 10-31-2017, 01:29 AM

Originally Posted by RICVB

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

**greyangel** · 10-31-2017, 08:13 AM

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

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** · 10-31-2017, 02:06 PM

Originally Posted by greyangel

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** · 11-01-2017, 05:13 AM

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** · 07-21-2018, 03:55 PM

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

Thread: Excel VBA - Loop in a string

Thread Tools

Display

Excel VBA - Loop in a string

Tags for this Thread

Posting Permissions