SuperKoopa
Technical User
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
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