Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

When max date is found then enter into that record

Status
Not open for further replies.
Biznez

Biznez

Technical User
Apr 9, 2015
106
CA
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
 
Sort by date Sort by votes
Hi again Skip, im getting stuck in the area that is in red

Code: 
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
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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




Code: 
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:



 
Upvote 0 Downvote

Code: 
    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
 
Upvote 0 Downvote
Thanks skip but im getting Type Mismatch error on red line below

Code: 
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
 
Upvote 0 Downvote
Hey Skip, could this be because vba does not recognize this as a date?
 
Upvote 0 Downvote
Test your date string before that statement using trim()

If Trim(DateString) = "" Then Exir For

And of course DateString is your GetString function.
 
Upvote 0 Downvote
Sorry Skip but im confused man. some help here please
 
Upvote 0 Downvote
you mean something like this?
Code: 
        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
 
Upvote 0 Downvote
sorry i didn't understand your post
"But YOUR date string is that Sess.scrn.GetString() statement in your code."

 
Upvote 0 Downvote
In my code that I posted, DateString is MY shorthand substitution for YOUR Sess.scrn.GetString() statement that gets YOUR date string.
 
Upvote 0 Downvote
Can you please show me how i can incorporate the code into the code you provided earlier
 
Upvote 0 Downvote
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 ?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Suresh02
  • Locked
  • Question
Automation error 'Specified module could not be found&quot;
Replies
0
Views
252
Suresh02
Suresh02
nightrider69
  • Locked
  • Question
Micro mainframe transmission issues using Attachmate myExtra
Replies
0
Views
431
nightrider69
nightrider69
dhsd
  • Locked
  • Question
VBA code works fine in debug mode but not when I run it
Replies
5
Views
605
SkipVought
S
Mike.and.H
  • Locked
  • Question
How to display date in EXTRA! X-treme window status bar?
Replies
0
Views
315
Mike.and.H
Mike.and.H
planetech
  • Locked
  • Question
enter number and print to file? 1
Replies
14
Views
569
planetech
planetech

Part and Inventory Search

Sponsor

Back
Top