×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

When max date is found then enter into that record

thread99-1751191

When max date is found then enter into that record

When max date is found then enter into that record

(OP)
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

RE: When max date is found then enter into that record

Hi,

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

RE: When max date is found then enter into that record

(OP)
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
    
    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
End Sub

RE: When max date is found then enter into that record

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.

RE: When max date is found then enter into that record

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.

RE: When max date is found then enter into that record

(OP)
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. DATE ISS DATE CAN AAA DATE 
                                                                               
_ A-1111111    ACTIVE  1,111.00  1,111.00 11111111  01/17/15 00/00/00 08/17/15 
_ A-1111111    ACTIVE  1,111.00  1,111.00 11111111  05/01/15 00/00/00 08/17/15 
                                                                             
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
         TOTAL :        2,222.00  2,222.00                                     
                                                                               
 PF7/8 - BWD/FWD: E - VIEW: S - CHANGE STATUS: L -      INFORMATION:




RE: When max date is found then enter into that record


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

RE: When max date is found then enter into that record

(OP)
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
            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

RE: When max date is found then enter into that record

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

RE: When max date is found then enter into that record

Test your date string before that statement using trim()

If Trim(DateString) = "" Then Exir For

And of course DateString is your GetString function.

RE: When max date is found then enter into that record

Sorry, I think faster than I type on my iPad

Exit For

RE: When max date is found then enter into that record

(OP)
Sorry Skip but im confused man. some help here please

RE: When max date is found then enter into that record

(OP)
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

RE: When max date is found then enter into that record

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

RE: When max date is found then enter into that record

(OP)
not really

RE: When max date is found then enter into that record

Not really what?

RE: When max date is found then enter into that record

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

RE: When max date is found then enter into that record

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

RE: When max date is found then enter into that record

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

RE: When max date is found then enter into that record

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 ?

RE: When max date is found then enter into that record

(OP)
im just confused about this statement you posted earlier

If Trim(DateString) = "" Then Exit For


This would just exit the loop

RE: When max date is found then enter into that record

Here is YOUR date string

CODE

Sess0.Screen.GetString(i, 54, 8)

CODE

If Trim(DateString) = "" Then Exit For
So you SUBSTITUTE that code for DateString

CODE

If Trim(Sess0.Screen.GetString(i, 54, 8)) = "" Then Exit For

RE: When max date is found then enter into that record

(OP)
Hi Skip, thanks for your help but now im getting error 'Type Mismatch"

CODE --> 

Dim dMaxDTE As Date, rw As Integer

    Do

        For i = 8 To 17
                If Trim(Sess0.Screen.GetString(i, 54, 8)) = "" Then Exit For
                If dMaxDTE < DateValue(Trim(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"
        'Sess0.Screen.SendKeys ("<Enter>")
        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

RE: When max date is found then enter into that record

I have not coded Attachmate objects for over 3 years. So check the systax for PutString in Extra VB HELP.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close