I'm currently running a script that loops thru an Excel spreadsheet row by row, reads the data from excel and updates a screen within a 3270 emulator. The looping is completed using the following procedure:
Sub Main
subGetSession
subGoToScreen "DELP", "********", "0000000001"
Dim rw
Dim TITLE
TITLE = "Script Completion Confirmation"
Set atlDirectorObject = CreateObject("atlDirectorObject.atlDirector")
Set objExcel = createobject("Excel.Application")
Set objExcelWb = objExcel.Workbooks.Open
("S:\Scripting\0711_Settlement2.xlsx")
Set objExcelSht = objExcelWb.Worksheets("Test")
Set oFileObject = CreateObject("Scripting.FileSystemObject")
rw = 2
objExcel.Visible = True
Do While objExcel.CountA(objExcelSht.Rows(rw)) > 0
subDoWork objExcelSht.Rows(rw)
rw = rw + 1
If (Err.Number <> 0) Then
On Error GoTo 0
WScript.Echo "Script Has Been Terminated"
WScript.Quit
End If
On Error GoTo 0
Loop
MsgBox "End of Worksheet Has Been Reached" & Chr(13) & "" & Chr(13) & "Script Completed!", vbOkOnly, TITLE
End Sub
I'm able to paste the data that is read by the loop into the 3270 screen using the procedure subDoWork
. I'm using columns A thru L on the Excel spreadsheet and subDoWork
is able to go down the spreadsheet row by row and copy and paste the data from Excel onto the 3270 screen. Below is an example of what I'm using to enter the data onto the 3270 screen from column B on the Excel spreadsheet:
subMoveCursor 11, 5
subEnterData rw.Cells(2).Value
Then after pasting the data, I use funcReadScreen
to read the screen in 3270 and produce a pop-up with the result that I receive from a given row from Excel.
Sub subErrorHandle
If funcReadScreen(24,2,37) = "A TRANSFER IS ALREADY PENDING" Then
subErrorHandle_TransferPending
Else
If funcReadScreen(24,2,27) = "INVALID NUMBER" Then
subErrorHandle_InvalidNumber
End If
End If
End Sub
What I'm looking to do is use the function funcReadScreen
to write that result to column M in Excel. I'm currently reading columns A - L and would like to enter the result I receive from funcReadScreen to column M in Excel based off the data in a given row.
Meaning, that error should be pasted into column M for that row, and then go to the next row, and if a different result/error is given via funcReadScreen
, then paste that result/error into column M for that row and move to the next row until finished. So, if I receive the error "Invalid Number" via the read screen function, I want to write that result to column M for that row.
I'm currently using the following sub to attempt this, but it is not working:
Sub subWriteToExcel2
Dim objExcel, objExcelWb, objExcelSht
Set objExcel = GetObject(, "Excel.Application")
Set objExcelWb = objExcel.ActiveWorkbook("0711_Settlement2.xlsx")
Set objExcelSht = objExcel.ActiveWorkbook.Worksheets("Test")
If funcReadScreen(24,2,27) = "*** INVALID NUMBER ***" Then
objExcelSht.Cells(2,13).Value = "Invalid Number"
End If
End Sub
Can anyone assist?
Edit: What seems to be not working is that I am unable to "get" the Excel object it seems like. I receive the error
object doesn't support this property or method: objExcel.ActiveWorkbook
However, I need it to not only paste the value into objExcelSht.Cells(2,13).Value
(or row 2, column M) but to subsequently move with the loop and ALWAYS paste into column M, based off my If
statement with what funcReadScreen
identifies.
After some working with this issue for a couple days I was able to resolve the issue. I simply added the following IF statement at the end of my main code, and it works great. Thank you for your assistance.
If funcReadScreen(24,2,27) = "INVALID NUMBER" Then
rw.Cells(13).Value = "Invalid Number"