Write Data To Excel Row By Row

Question

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.

Answer 1

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"