Consulting

Results 1 to 15 of 15

Thread: Excel VBA - Loop in a string

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    6
    Location

    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

  2. #2
    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

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    What's a SAP, and how do you write scripts for it?
    Please take the time to read the Forum FAQ

  4. #4
    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.

  5. #5
    VBAX Regular
    Joined
    Oct 2017
    Posts
    6
    Location
    Quote Originally Posted by greyangel View Post
    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.

  6. #6
    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

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

  8. #8
    VBAX Regular
    Joined
    Oct 2017
    Posts
    6
    Location
    Quote Originally Posted by greyangel View Post
    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.

  9. #9
    VBAX Regular
    Joined
    Oct 2017
    Posts
    6
    Location
    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

  10. #10
    VBAX Regular
    Joined
    Dec 2013
    Posts
    11
    Location
    Quote Originally Posted by zotja View Post
    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

  11. #11
    VBAX Regular
    Joined
    Oct 2017
    Posts
    6
    Location
    Quote Originally Posted by RICVB View Post
    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

  12. #12
    Quote Originally Posted by zotja View Post
    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

  13. #13
    VBAX Regular
    Joined
    Oct 2017
    Posts
    6
    Location
    Quote Originally Posted by greyangel View Post
    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.

  14. #14
    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.

  15. #15
    Thanks for the VBA script. Works like a charm with my accounting macro!
    ___________________________________
    Marius from SAP Consulting

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •