When max date is found then enter into that record

[Biznez]

Hi,
Trying to write a vba code in excel where when in Mainframe the max date from location (8,54,8) to bottom of page is found then go into that record by entering "E" at location (x,02). Hope i made sense.
Thanks

 

[SkipVought]

Hi,

So what code do you have so far and where are you stuck?

 

[Biznez]

Hi again Skip, im getting stuck in the area that is in red

Global g_HostSettleTime%
Global g_szPassword$

Sub Main()

'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")   ' Gets the system object
    If (System Is Nothing) Then
        MsgBox "Could not create the EXTRA System object.  Stopping macro playback."
        Stop
    End If
    Set Sessions = System.Sessions

    If (Sessions Is Nothing) Then
        MsgBox "Could not create the Sessions collection object.  Stopping macro playback."
        Stop
    End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
    g_HostSettleTime = 0     ' milliseconds

    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If

' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If (Sess0 Is Nothing) Then
        MsgBox "Could not create the Session object.  Stopping macro playback."
        Stop
    End If
    If Not Sess0.Visible Then Sess0.Visible = True
    Sess0.Screen.WaitHostQuiet (0)
    
    
    Dim fso As Object
    Dim ts As Object
    Dim obj As Object
    Dim i
    Dim strCBName
    Dim blnHasMoreLines
    Set obj = GetObject("C:\Template.xlsm") 'File is already open
    
    [COLOR=#EF2929]Do
    Sess0.Screen.WaitHostQuiet (0)
        For i = 8 To 17
            WorksheetFunction.Max = Sess0.Screen.GetString(i, 54, 8)
        Next i
        If Sess0.Screen.GetString(23, 2, 4) = ("4941") Then Exit Do
            'Sends the next page command
            Sess0.Screen.SendKeys ("<Pf8>")
            Sess0.Screen.WaitHostQuiet (0)
    Loop[/color]
End Sub

 

[SkipVought]

If you're writing code in Excel VBA, then Global is not a VBA Key Word, rather Public for global variables.

If you're writing code in Excel VBA, then you need not create an object variable for the workbook object. You're probably coding in Template.xlsm!

I'll get back to you on the loops in red.

 

[SkipVought]

So tell me what your date string looks like in columns 54 - 61.

It might be beneficial to post (copy/paste) a screenshot here too.

 

[Biznez]

This is a screen shot. The max date i require is in Red under column DATE ISS. And the green underscore on the left is where i want to input the letter E to enter the account.
The start positioning of the date is 54,61 and the start positioning of the underscore is 8, 2

T581                                                   AAAA ON V937 AT 11:09:35
AP85FPC -LOAN          T581 - LIST OF ACCOUNTS                                06/18/15
                                                                               
    1111111111 SMITH J                          Location         11111111111111 
                                                                               
    CERT. NO   STATUS    AWARD    DISB'D   LOAN NO. [COLOR=#EF2929]DATE ISS[/color] DATE CAN AAA DATE 
                                                                               
_ A-1111111    ACTIVE  1,111.00  1,111.00 11111111  01/17/15 00/00/00 08/17/15 
[COLOR=#8AE234]_[/color] A-1111111    ACTIVE  1,111.00  1,111.00 11111111  [COLOR=#EF2929]05/01/15[/color] 00/00/00 08/17/15 
                                                                             
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
         TOTAL :        2,222.00  2,222.00                                     
                                                                               
 PF7/8 - BWD/FWD: E - VIEW: S - CHANGE STATUS: L -      INFORMATION:

 

[SkipVought]

    Dim dMaxDTE As Date, rw As Integer
    
    Do
        For i = 8 To 17
            If dMaxDTE < DateValue(Sess0.Screen.GetString(i, 54, 8)) Then
                dMaxDTE = DateValue(Sess0.Screen.GetString(i, 54, 8))
                rw = i
            End If
        Next i
        ' entering "E" at location (x,02).
        Sess0.Screen.PutString(rw, 2) = "E"
        
        If Sess0.Screen.GetString(23, 2, 4) = ("4941") Then Exit Do
        
        'Sends the next page command
        Sess0.Screen.SendKeys ("<Pf8>")
        Do Until (Sess0.Screen.WaitForCursor(r, c)) 'r,c is screen cursor rest coordinates
            DoEvents
        Loop
    Loop

 

[Biznez]

Thanks skip but im getting Type Mismatch error on red line below

Dim dMaxDTE As Date, rw As Integer
    
    Do
        For i = 8 To 17
           [COLOR=#EF2929] If dMaxDTE < DateValue(Sess0.Screen.GetString(i, 54, 8)) Then[/color]
                dMaxDTE = DateValue(Sess0.Screen.GetString(i, 54, 8))
                rw = i
            End If
        Next i
        ' entering "E" at location (x,02).
        Sess0.Screen.PutString(rw, 2) = "E"
        
        If Sess0.Screen.GetString(23, 2, 4) = ("4941") Then Exit Do
        
        'Sends the next page command
        Sess0.Screen.SendKeys ("<Pf8>")
        Do Until (Sess0.Screen.WaitForCursor(r, c)) 'r,c is screen cursor rest coordinates
            DoEvents
        Loop
    Loop

 

[Biznez]

Hey Skip, could this be because vba does not recognize this as a date?

 

[SkipVought]

Test your date string before that statement using trim()

If Trim(DateString) = "" Then Exir For

And of course DateString is your GetString function.

 

[SkipVought]

Sorry, I think faster than I type on my iPad

Exit For

 

[Biznez]

Sorry Skip but im confused man. some help here please

 

[Biznez]

you mean something like this?

        For i = 8 To 17
        If Trim(DateString) = "" Then Exit For
            If dMaxDTE < DateValue(Sess0.Screen.GetString(i, 54, 8)) Then
                dMaxDTE = DateValue(Sess0.Screen.GetString(i, 54, 8))
                rw = i
            End If
        Next i

 

[SkipVought]

But YOUR date string is that Sess.scrn.GetString() statement in your code.

 

[Biznez]

not really

 

[SkipVought]

Not really what?

 

[Biznez]

sorry i didn't understand your post
"But YOUR date string is that Sess.scrn.GetString() statement in your code."

 

[SkipVought]

In my code that I posted, DateString is MY shorthand substitution for YOUR Sess.scrn.GetString() statement that gets YOUR date string.

 

[Biznez]

Can you please show me how i can incorporate the code into the code you provided earlier

 

[SkipVought]

I presented the solution to you. If you're coding, you need to take on some personal initiative.

Have you actually tried anything? If so what? And what were the results ?