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
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
So what code do you have so far and where are you stuck?
RE: When max date is found then enter into that record
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 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
It might be beneficial to post (copy/paste) a screenshot here too.
RE: When max date is found then enter into that record
The start positioning of the date is 54,61 and the start positioning of the underscore is 8, 2
CODE -->
RE: When max date is found then enter into that record
CODE
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
RE: When max date is found then enter into that record
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
Exit For
RE: When max date is found then enter into that record
RE: When max date is found then enter into that record
CODE -->
RE: When max date is found then enter into that record
RE: When max date is found then enter into that record
RE: When max date is found then enter into that record
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
RE: When max date is found then enter into that record
RE: When max date is found then enter into that record
Have you actually tried anything? If so what? And what were the results ?
RE: When max date is found then enter into that record
If Trim(DateString) = "" Then Exit For
This would just exit the loop
RE: When max date is found then enter into that record
CODE
CODE
If Trim(DateString) = "" Then Exit For
CODE
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 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