Attachmate Data Validation 1

[SuperKoopa]

Hello, I am hoping to get some help on a report Im trying to generate from Attachmate. I need a macro to navigate to enter an account number based off of cell A1 on the active workbook (Sheet1), then naviagte a screen (GEM1, check to see if a specific entry code (Z12) exists in location (10, 20, 4), if it does exist I need to recognize whether or not a date is entered next to it in location (10, 13, 6). If there is a date entered then the word "Yes" should be sent to cell B1 on the active Workbook(Sheet1), then attachmate needs to navigate to another screen (GEM2) upon verifying the date does exist and collect a dollar amount from a specific location on a screen (8, 33, 9) and send that data to the same active workbook (Sheet1) in cell C1.

If the entry code (Z12) does not exist or there is no date on the existing A12 code, Then move on to the next account number on the active workbook in cell A2 and start the process again. I would also like it to stop looping if (23, 1, 40) is blank.

I had a variation of code I was using that would give me the code above the one I am tooking to pull, but was not able to get it to pull the one I wanted. Also, it wasnt validating whether or not there was a date next to it. Eventually I got frustrated and now Im left with the code below that doesnt do anything. Please help if you can, I really appreciate it. Thank you1

**********************************************************************************************************
**********************************************************************************************************


' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub PullZ12()
' Get the main system object
Dim Sessions As Object
Dim i As Integer
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 = 100 ' 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 "Session is not open! Please open login to MSP and try again."
Stop
End If
If Not Sess0.Visible Then Sess0.Visible = True
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

' Declare variables to contain the OLE objects
Dim objExcel As Object
Dim objWorkBook As Object
Dim gs As String
Dim r As Integer
Dim j As Integer
r = 1

On Error Resume Next

' Attempt to get a reference to an open instance of Excel
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
'If GetObject failed, open a new instance of Excel
Set objExcel = CreateObject("Excel.Application")
If objExcel Is Nothing Then
MsgBox ("Could not open Excel.")
Exit Sub
End If
End If

' Make Excel visible on the screen
objExcel.Visible = True

' Create a new Workbook
Set objWorkBook = objExcel.ActiveWorkbook

' BEFORE YOU RUN THIS YOU HAVE TO BE IN THE "MM" SCREEN WITH "VQ" IN THE FUNCTION FIELD
' Copy the result from Excel to host row 16 column 1-3

'**************************************************************************
'GET DATA**************************************************************
'**************************************************************************
Do

g_HostSettleTime = 75 ' ms

Sess0.Screen.SendKeys ("<Home><Clear><Clear>") 'Clears the screen for a new account
Sess0.Screen.SendKeys ("SR01<Enter>")

Sess0.Screen.SendKeys ("GEM1") 'Navigates to GEM1 screen
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
result = objWorkBook.Worksheets("Sheet1").Cells(1, "A").Value 'Gets account number
Sess0.Screen.Putstring result, 12, 40 'Enters account number
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")

Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

r = r + 1
Loop Until objWorkBook.Worksheets("Sheet99").Cells(r, 1).Value = ""

' Excel will remain open after this Sub ends.
' To close out Excel, unremark the following 4 lines of code. .
'objWorkBook.Close
'objExcel.Quit
'set objWorkBook = Nothing
'set objExcel = Nothing

'**************************************
'**************************************

With ActiveWorkbook(curpath & FileName)

Do
With Sheets("Sheet1")
For z = 5 To 22
If Sess0.Screen.GetString(z, 20, 3) = "Z12" Then
Sess0.Screen.SendKeys ("<Home>") 'Clears the screen for a new loan
Sess0.Screen.SendKeys ("GEM2<Enter>")

If Sess0.Screen.GetString(z, 13, 6) = " " Then Exit Do

.Cells(1, "B").Value = Sess0.Screen.GetString(z, 20, 3)

Sess0.Screen.SendKeys ("<PF8>")
Next z
End With

Loop Until Sess0.Screen.GetString(22, 11, 6) = " "
End With

End Sub

 

[SkipVought]

hi,

I dunno, you said A2 earlier, but your code says B1??? Nevertheless, you must incriment your Excel Row Number each time your loop thru your screen rows, don't you?

I think you want to do something like this...

    Dim lRow as Long
'.......
    With objWorkBook
    
        Do
            With .Sheets("Sheet1")
                lRow = .Cells(1,"B".CurrentRegion.Rows.Count + 1
                For Z = 5 To 22
                    If Sess0.Screen.GetString(Z, 20, 3) = "Z12" Then
                    Sess0.Screen.SendKeys ("<Home>") 'Clears the screen for a new loan
                    Sess0.Screen.SendKeys ("GEM2<Enter>")
                    
                    If Sess0.Screen.GetString(Z, 13, 6) = " " Then Exit Do
                    
                    .Cells(lRow, "B").Value = Sess0.Screen.GetString(Z, 20, 3)
                    lRow = lRow + 1
                    
                    Sess0.Screen.SendKeys ("<PF8>")
                Next Z
            End With
        
        Loop Until Sess0.Screen.GetString(22, 11, 6) = " "
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Another thing: whenever you issue a SendKeys command to the screen, since your emulator is asynchronously coupled to the system, you have to wait for a response, before proceeding to the next statement in your code.

For instance in your code I returned to you, I see this.

    Sess0.Screen.SendKeys ("GEM2<Enter>")
                    
    If Sess0.Screen.GetString(Z, 13, 6) = " " Then Exit Do

Well here's what will happen. Unless your system is repsponding with lightning speed, your GetString will process before the system has had the chance to put anything in Z, 13.

Some people put a fixed delay or wait time for the emulator to wait, but that's like programming your car to stop 10 seconds at every traffic light, whether it's RED or GREEN, then GO whether it's RED or GREEN!!!

How dumb is that? How about waiting for the cursor to appear AT THE REST COORDINATES for that screen (each screen has different rest coordinates)!

    Sess0.Screen.SendKeys ("GEM2<Enter>")
    Do While Sess0.Screen.WaitForCursor(RowRest, ColRest)
        DoEvents
    Next

The other thing is you are getting SIX characters and testing that against ONE space?????

If Sess0.Screen.GetString(Z, 13, 6) = " " Then Exit Do

I think I'd do this...

If Trim(Sess0.Screen.GetString(Z, 13, 6)) = "" Then Exit Do

in other words, if there's NOTHING in that 6 character area, then exit.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SuperKoopa]

Amazing! Thank you very much for your help. I feel like I'm learning more and more as I go. I had to scrap this project so I was unable to test it, but I've taken several of you suggestions and applied them elsewhere and improved other projects. Thank you Skip!

 

[SkipVought]

Good to know!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]