LOOP or STAGE method? 1

[Kuljack]

Hello all,

It's been a long time since I posted on here, I've learned a lot since my last post.

I need some help to get around an issue, I'm struggling between using a stage method "if this go to 2, else go to 1" or try to create a thread of loops.

The goal is to create a macro that feeds from excel and validates a few screens status, then trims information until it finds a designated string. I've hit a wall and struggling with the loop path. I've started it as a sample but the more I do the less I think it will work. The initial stage method is also there in "full". I can revise if needed but I think most can make do and provide some advice.

I don't need the script written for me, just some input on the best method. Thanks!

Sub RunStrmClean()

Set objSession = GetObject("C:\Users\krwrigh\Desktop\CPI.edp")
Set objscreen = objSession.Screen
Dim Active As String
Dim Change As String
Dim Client As String
Dim L14 As String
Dim L14status As String
Dim LMT1 As String
Dim LMT3 As String
Dim LN As String
Dim Loan As String
Dim Loanstatus As String
Dim M44 As String
Dim M44CODE As String
Dim M44date As String
Dim N As String
Dim Setupdate As String
Dim Stepcode As String







LN = 9
M44CODE = Cells(4, 1)
Change = Cells(4, 2)
LMT3 = Cells(4, 3)
L14 = Cells(4, 6)
LMT1 = Cells(4, 4)
Active = Cells(4, 5)
Client = Cells(LN, 1)
Do Until Client = Empty
objscreen.SendKeys ("<clear><clear><clear>")
objscreen.SendKeys ("TMID<enter>")
objscreen.WaitHostQuiet (xtraSettleTime)
MSP = Trim(objscreen.getstring(9, 49, 3))
Do Until Client = MSP
Cancel = MsgBox("Please log into client " & Client, vbOKCancel, "Client Error")
If Cancel = "2" Then
GoTo Endnow
Else
objscreen.SendKeys ("<clear><clear><clear>")
objscreen.SendKeys ("TMID<enter>")
objscreen.WaitHostQuiet (xtraSettleTime)
MSP = Trim(objscreen.getstring(9, 49, 3))
End If
Loop
1 Do Until Loan = Empty
N = 6
Loan = Cells(LN, 2)
objscreen.SendKeys ("<home>") & LMT1 & Loan & ("<enter>")
Setupdate = Trim(objscreen.getstring(11, 5, 6))
Loanstatus = Trim(objscreen.getstring(7, 5, 1))
If Loanstatus = Active Then
GoTo 2
Else
Do Until LN = Empty
LN = LN + 1
GoTo 1
Loop
End If
Loop
2






Endnow:


' this is the initial path, validating the TMID screen will be incorporated later.

1 'Validate the loan is active in loss mit
N = 6
Loan = Cells(LN, 2)
LMT1 = Cells(4, 4)
Active = Cells(4, 5)

objscreen.SendKeys ("<clear><clear><clear>")
objscreen.SendKeys ("<home>") & LMT1 & Loan & ("<enter>")
Setupdate = Trim(objscreen.getstring(11, 5, 6))
Loanstatus = Trim(objscreen.getstring(7, 5, 1))
If Loanstatus = Active Then
GoTo 2

Else
LN = LN + 1
If LN = "" Then
GoTo 11
Else
GoTo 1


2 'Begin looking for L14 StepCode
objscreen.SendKeys ("<home>") & LMT3 & Loan & ("<enter>")
Stepcode = Trim(objscreen.getstring(N, 20, 3))
If Stecode = L14 Then
GoTo 4

Else
N = N + 1
If N = 23 Then
GoTo 3

Else
GoTo 2

3 'Change page after reaching the last step code in current page without being L14
objscreen.SendKeys ("<Pf8>")
N = 6
GoTo 2

4 'After finding L14 step code check on the date status
L14status = Trim(objscreen.getstring(N, 13, 6))
If L14status = "" Then
GoTo 5

Else
LN = LN + 1
GoTo 1

5 'Begin looking for M44 if L14 is blank
N = 6
M44 = Trim(objscreen.getstring(N, 20, 3))
If M44 = M44CODE Then
GoTo 6

Else
N = N + 1
GoTo 5

6 'Capture M44 date after locating M44 step code
M44date = Trim(objscreen.getstring(N, 13, 6))
If M44date = "" Then
GoTo 7

Else
GoTo 8

7 'If M44 is blank, using LMT1 Setup date
objscreen.putstring Change, N, 2
objscreen.WaitHostQuiet (xtraSettleTime)
objscreen.SendKeys ("<enter>")
objscreen.putstring Setupdate, N, 13
objscreen.WaitHostQuiet (xtraSettleTime)
objscreen.SendKeys ("<enter>")
M44date = Trim(objscreen.getstring(N, 13, 6))
GoTo 8

8 'Begin looking for L14 again
objscreen.SendKeys ("<home>") & LMT3 & Loan & ("<enter>")
Stepcode = Trim(objscreen.getstring(N, 20, 3))
If Stecode = L14 Then
GoTo 10

Else
N = N + 1
If N = 23 Then
GoTo 9

Else
GoTo 8

9 'Change page after reaching the last step code in current page without being L14
objscreen.SendKeys ("<Pf8>")
N = 6
GoTo 8

10
objscreen.putstring Change, N, 2
objscreen.WaitHostQuiet (xtraSettleTime)
objscreen.SendKeys ("<enter>")
objscreen.putstring M44date, N, 13
objscreen.WaitHostQuiet (xtraSettleTime)
objscreen.SendKeys ("<enter>")
LN = LN + 1
If LN = "" Then
GoTo 11

Else
GoTo 1

11
MsgBox "Macro Complete"

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub

 

[SkipVought]

hi,

Seem you're writing this in Excel VBA. Good choice!

But your have 4 Do loops and only 3 Loop statements.

I would advise to avoid GoTo logic.

Not knowing your screen navigation, which is the NUMBER ONE priority, it hard to give you specific recommendations. But you must first and foremost determine how you navigate from screen to screen, what messages indicate MORE, SUCCESS or ERROR.

Your outer loop would be the one that reads the data from your Excel sheet.

Then you access the screen within the outer loop, etc.

Your spaghetti code really has to go!!!

Sub testt()
    'this is just an example using some of your code
    'you no doubt loop thru your screen in some way that is not apparent
    'N seems that it may have something to do with that???
    
    'these look like constant values
    M44CODE = Cells(4, 1)
    Change = Cells(4, 2)
    LMT3 = Cells(4, 3)
    L14 = Cells(4, 6)
    LMT1 = Cells(4, 4)
    Active = Cells(4, 5)
    
    'this loop will read every loan in your table
    For Each Loan In Range(Cells(9, "B"), Cells(9, "B").End(xlDown))
        Client = Cells(Loan.Row, 1)

        objscreen.SendKeys ("<clear><clear><clear>")
        objscreen.SendKeys ("<home>") & LMT1 & Loan & ("<enter>")
        Setupdate = Trim(objscreen.getstring(11, 5, 6))
        Loanstatus = Trim(objscreen.getstring(7, 5, 1))
        If Loanstatus = Active Then
            objscreen.SendKeys ("<home>") & LMT3 & Loan & ("<enter>")
            N = 6
            '[b]
            Do While N < 20 'this is just a guess[/b]
                Stepcode = Trim(objscreen.getstring(N, 20, 3))
                If Stecode = L14 Then
                    L14status = Trim(objscreen.getstring(N, 13, 6))
                    If L14status = "" Then
                        N = 6
                        M44 = Trim(objscreen.getstring(N, 20, 3))
                        If M44 = M44CODE Then
                            M44date = Trim(objscreen.getstring(N, 13, 6))
                            If M44date = "" Then
                                objscreen.putstring Change, N, 2
                                objscreen.WaitHostQuiet (xtraSettleTime)
                                objscreen.SendKeys ("<enter>")
                                objscreen.putstring Setupdate, N, 13
                                objscreen.WaitHostQuiet (xtraSettleTime)
                                objscreen.SendKeys ("<enter>")
                                M44date = Trim(objscreen.getstring(N, 13, 6))
                            Else
                                objscreen.SendKeys ("<home>") & LMT3 & Loan & ("<enter>")
                                Stepcode = Trim(objscreen.getstring(N, 20, 3))
                                If Stecode = L14 Then
                                    objscreen.putstring Change, N, 2
                                    objscreen.WaitHostQuiet (xtraSettleTime)
                                    objscreen.SendKeys ("<enter>")
                                    objscreen.putstring M44date, N, 13
                                    objscreen.WaitHostQuiet (xtraSettleTime)
                                    objscreen.SendKeys ("<enter>")
                                Else
                                    
                                End If
                            End If
                        End If
                    End If
                End If
                '[b]
            Loop    '[/b]
            
        End If

    Loop

End Sub

 

[Kuljack]

Thank you for your response Skipvought!

Thanks for the input, and I agree the Spaghetti code was unfortunate. This was not a refined script, just trying to sketch it out. Will keep that in mind on future posts!

I appreciate the suggested script, but when does the value of N increase? If the value is not found to be L14 when trimming for this, N would need to increase by 1 effectively looking to the next row below until it either finds the code or reaches the value of 23 at which point it would change screens "F8".

I will steer clear of goto logic, and try to refine this loop path and come back. Let me know what information you need to understand the navigation.

 

[Kuljack]

Alright, so I've taken your advice. No more spaghetti code on my posts, using some of your direction I've revised the code and cleaned up all the rough edges.

It took a little work, but I have something that works 100%. I noticed a glitch where only the L14 updates but the M45 doesn't on the first run but I think I can figure that out. A second run of the tool updates the M45. Thank-you for your assistance!

Edit/Update: Okay I just added some additional wait times between actions and it runs smooth. No more glitching, thank-you Skipvought. I have given you a star for your response.


Problem solved!

Sub RunStrmClean()

Set objSession = GetObject("C:\Users\krwrigh\Desktop\CPI.edp")
Set objscreen = objSession.Screen
Dim Client As String 'this will reference the client number
Dim MSP As String 'this will find what session the user is in
Dim Loan As String 'this will be the current loan number
Dim LMT1date As String ' this will reference the LMT1 setupdate
Dim Status As String 'this checks LMT1 status
Dim Stepcode As String 'this returns the value of the current step code
Dim Stepdate As String 'this returns the value of the current step code actual date
Dim Change As String 'this provides a C to change a stepcode value
Dim LMT3 As String 'this provides the screen LMT3
Dim LMT1 As String 'this provides the screen LMT1
Dim M44 As String 'this gives text M44
Dim L14 As String 'this gives text L14
Dim M45 As String 'this gives text M45
Dim Attempts As String 'used to count the number of times a screen changes

    
Change = "C"
LMT1 = "LMT1"
LMT3 = "LMT3"
M44 = "M44"
L14 = "L14"
M45 = "M45"
    
    For Each LoanN In Range(Cells(9, "B"), Cells(9, "B").End(xlDown))
        Client = Cells(LoanN.Row, 10)
        If Client = Empty Then
        GoTo Endnow
        Else
            objscreen.SendKeys ("<clear><clear><clear>")
            objscreen.SendKeys ("TMID<enter>")
            objscreen.WaitHostQuiet (xtraSettleTime)
            MSP = Trim(objscreen.getstring(9, 49, 3))
                Do Until Client = MSP
                    Cancel = MsgBox("Please log into client " & Client, vbOKCancel, "Client Error")
                    If Cancel = "2" Then
                    GoTo Endnow
                    Else
                        objscreen.SendKeys ("<clear><clear><clear>")
                        objscreen.SendKeys ("TMID<enter>")
                        objscreen.WaitHostQuiet (xtraSettleTime)
                        MSP = Trim(objscreen.getstring(9, 49, 3))
                    End If
                Loop
                Loan = Cells(LoanN.Row, 11)
                    objscreen.SendKeys ("<home>") & LMT1 & Loan & ("<enter>")
                    objscreen.WaitHostQuiet (xtraSettleTime)
                    LMT1date = Trim(objscreen.getstring(11, 5, 6))
                    Status = Trim(objscreen.getstring(7, 5, 1))
                    If Status = "A" Then
                    'Loan is active, begin scrapping loan to clean as needed
                    objscreen.SendKeys ("<home>") & LMT3 & ("<enter>")
                    objscreen.WaitHostQuiet (xtraSettleTime)
                            Attempts = 0
                            Do Until Stepcode = M44
                                N = 5
                                Do Until N = 23
                                    N = N + 1
                                    Stepcode = Trim(objscreen.getstring(N, 20, 3))
                                    If Stepcode = M44 Then
                                    GoTo Endloop1
                                    Else
                                    End If
                                Loop 'Continues looking for M44 till N=23 (bottom line)
                                objscreen.SendKeys ("<Pf8>") 'Moves to next page to continue search
                                objscreen.WaitHostQuiet (xtraSettleTime)
                                Attempts = Attempts + 1
                                If Attempts = 4 Then
                                    Cancel = MsgBox("M44 not found. Manually correct loan and rerun macro", vbOKOnly, "Error")
                                    If Cancel = "1" Then
                                    GoTo Endnow
                                    Else
                                    End If
                                End If
Endloop1:
                            Loop 'Restarts N and continues to looks for M44
                                Stepdate = Trim(objscreen.getstring(N, 13, 6))
                                If Stepdate = "" Then
                                    objscreen.putstring Change, N, 2
                                    objscreen.putstring LMT1date, N, 13
                                    objscreen.WaitHostQuiet (xtraSettleTime)
                                    objscreen.SendKeys ("<enter>")
                                    Else
                                    End If
                                Attempts = 0
                                Do Until Stepcode = L14
                                N = 5
                                    Do Until N = 23
                                        N = N + 1
                                        Stepcode = Trim(objscreen.getstring(N, 20, 3))
                                        If Stepcode = L14 Then
                                        GoTo Endloop2
                                        Else
                                        End If
                                    Loop 'Continues looking for L14 till N=23 (bottom line)
                                    objscreen.SendKeys ("<Pf8>") 'Moves to next page to continue search
                                    objscreen.WaitHostQuiet (xtraSettleTime)
                                    Attempts = Attempts + 1
                                    If Attempts = 4 Then
                                        Cancel = MsgBox("L14 not found. Manually correct loan and rerun macro", vbOKOnly, "Error")
                                        If Cancel = "1" Then
                                        GoTo Endnow
                                        Else
                                        End If
                                    End If
Endloop2:
                                Loop 'Restarts N and continues to looks for L14
                                    Stepdate = Trim(objscreen.getstring(N, 13, 6))
                                    If Stepdate = "" Then
                                        objscreen.putstring Change, N, 2
                                        objscreen.putstring LMT1date, N, 13
                                        objscreen.WaitHostQuiet (xtraSettleTime)
                                        objscreen.SendKeys ("<enter>")
                                        Else
                                        End If
                                    Attempts = 0
                                    Do Until Stepcode = M45
                                    N = 5
                                        Do Until N = 23
                                            N = N + 1
                                            Stepcode = Trim(objscreen.getstring(N, 20, 3))
                                            If Stepcode = M45 Then
                                            GoTo Endloop3
                                            Else
                                            End If
                                        Loop 'Continues looking for M45 till N=23 (bottom line)
                                        objscreen.SendKeys ("<Pf8>") 'Moves to next page to continue search
                                        objscreen.WaitHostQuiet (xtraSettleTime)
                                        Attempts = Attempts + 1
                                        If Attempts = 4 Then
                                            Cancel = MsgBox("M45 not found. Manually correct loan and rerun macro", vbOKOnly, "Error")
                                            If Cancel = "1" Then
                                            GoTo Endnow
                                            Else
                                            End If
                                        End If
Endloop3:
                                    Loop 'Restarts N and continues to looks for M45
                                        Stepdate = Trim(objscreen.getstring(N, 13, 6))
                                        If Stepdate = "" Then
                                            objscreen.putstring Change, N, 2
                                            objscreen.putstring LMT1date, N, 13
                                            objscreen.WaitHostQuiet (xtraSettleTime)
                                            objscreen.SendKeys ("<enter>")
                                            Else
                                            End If
                                    
                          
            End If
        End If
    Next

Endnow:
    MsgBox "Done"

End Sub