×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Attachmate Data Validation

Attachmate Data Validation

Attachmate Data Validation

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

RE: Attachmate Data Validation

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...

CODE

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Attachmate Data Validation

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.

CODE

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)!

CODE

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?????

CODE

If Sess0.Screen.GetString(Z, 13, 6) = " " Then Exit Do 
I think I'd do this...

CODE

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Attachmate Data Validation

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

RE: Attachmate Data Validation

Good to know!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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!

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