Help needed with syntax/logic in Attachmate macro

[CADD6953]

Hello Again!

After a few years since my last post I've been able to adapt the original macro that was created with great help from the community and make it perform numerous functions. Now, I'm looking to essentially have an extension of that original macro in excel screen scrape some different data from the Attachmate session but have run into a roadblock with the logistics.

The below code (still a work in progress and apologies as the code feature was presenting an error) has 'PO' as the input from excel into Attachmate to begin the process. The Attachmate session will then return records based off the 'PO' value into rows 8-21 on the screen. The macro will go record by record and look for location 'S' to be null while location 'I' has a value, it will then use input the selection code 'I' value to move into another screen for that specific record. This secondary screen will have more detailed information which will be screen scraped into excel before sending the PF3 function key to return to the list of records. Once the macro reaches the last record in row 21, it will then use the PF8 function key to move onto the next page of records and will loop the process. The issue I am running into is that for records on the second screen the macro will use the 'I' value to go into a record's secondary screen and scrape the data correctly but the PF3 function key returns to the original/first page of records and would start looping through that page again rather than moving back to the second page and continuing on with the next record. There are no identifiers between the pages (i.e. a location in the session that says page 1 of 2 or something similar) which I could have the macro look for unfortunately so I'm thinking there needs to be syntax built into the macro to use the PF8 function key the necessary amount of times to return to the screen it left off on but I'm not familiar with what the best method would be or really how to do that. Any ideas are appreciated!

Sub RecordLookUp()
Dim Sessions, System As Object, Sess0 As Object, PO As String, WB As Workbook, WS As Worksheet
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
Set WB = ActiveWorkbook
Set WS = Sheets("Look-up")

rw = 2
rw1 = 1
With Worksheets("Look-up")
Do
For X = rw To ActiveSheet.Rows.Count
PO = Left(Sheets("Look-up").Cells(X, 1).Value, 9)
If PO = "000000000" Then Exit Sub

Sess0.Screen.MoveTo 5, 22
Sess0.Screen.SendKeys ("BA")
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop
Sess0.Screen.MoveTo 5, 22
Sess0.Screen.SendKeys ("BA")
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop
Sess0.Screen.MoveTo 5, 22
Sess0.Screen.SendKeys ("CD")
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop
'Input value from Excel to Attachmate
Sess0.Screen.MoveTo 3, 44
Sess0.Screen.SendKeys PO
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop
Do
For r = 8 To 21
dd = 1
S = Trim(Sess0.Screen.GetString(r, 33, 8))
I = Trim(Sess0.Screen.GetString(r, 2, 2))
ML = Trim(Sess0.Screen.GetString(2, 23, 7))

'If both S and I are null -> end
If S = "" And I = "" Then
Exit Do

'If S is null but I is not, move into secondary screen with more detailed information
ElseIf S = "" Then
Sess0.Screen.MoveTo 5, 18
Sess0.Screen.SendKeys I
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop
If ML = "INQUIRY" Then
CONT = Sess0.Screen.GetString(4, 28, 4)
ID = Sess0.Screen.GetString(3, 44, 9)
ENTITY = Sess0.Screen.GetString(4, 9, 6)
PRDGRP = Sess0.Screen.GetString(4, 28, 6)
Date1 = Sess0.Screen.GetString(18, 64, 8)
Date2 = Sess0.Screen.GetString(18, 73, 8)
MCode = Sess0.Screen.GetString(4, 62, 2)
ISB = Sess0.Screen.GetString(9, 10, 8)
FR = Trim(Sess0.Screen.GetString(5, 51, 13))
MA = Sess0.Screen.GetString(5, 69, 1)
LA = Trim(Sess0.Screen.GetString(5, 12, 27))
SN = Sess0.Screen.GetString(8, 23, 9)
SR = Trim(Sess0.Screen.GetString(6, 14, 25))
CT = Trim(Sess0.Screen.GetString(6, 45, 25))
ST = Sess0.Screen.GetString(6, 78, 2)
ZP = Sess0.Screen.GetString(7, 6, 5)
rw1 = rw1 + 1
Sheets("Workbook").Cells(rw1, 1) = CONT
Sheets("Workbook").Cells(rw1, 2) = ID
Sheets("Workbook").Cells(rw1, 3) = ENTITY
Sheets("Workbook").Cells(rw1, 4) = PRDGRP
Sheets("Workbook").Cells(rw1, 5) = Date1
Sheets("Workbook").Cells(rw1, 6) = Date2
Sheets("Workbook").Cells(rw1, 7) = MCode
Sheets("Workbook").Cells(rw1, 8) = ISB
Sheets("Workbook").Cells(rw1, 9) = FR
Sheets("Workbook").Cells(rw1, 10) = MA
Sheets("Workbook").Cells(rw1, 11) = LA
Sheets("Workbook").Cells(rw1, 12) = SN
Sheets("Workbook").Cells(rw1, 13) = SR
Sheets("Workbook").Cells(rw1, 14) = CT
Sheets("Workbook").Cells(rw1, 15) = ST
Sheets("Workbook").Cells(rw1, 16) = ZP

Else
CONT = Sess0.Screen.GetString(4, 28, 4)
ID = Sess0.Screen.GetString(3, 44, 9)
ENTITY = Sess0.Screen.GetString(4, 9, 6)
PRDGRP = Sess0.Screen.GetString(4, 28, 6)
Date1 = Sess0.Screen.GetString(20, 21, 8)
Date2 = Sess0.Screen.GetString(20, 31, 8)
MCode = Sess0.Screen.GetString(4, 62, 2)
ISB = Sess0.Screen.GetString(9, 10, 8)
FR = Trim(Sess0.Screen.GetString(5, 51, 13))
MA = Sess0.Screen.GetString(5, 69, 1)
LA = Trim(Sess0.Screen.GetString(5, 12, 27))
SN = Sess0.Screen.GetString(9, 72, 9)
SR = Trim(Sess0.Screen.GetString(6, 14, 25))
CT = Trim(Sess0.Screen.GetString(6, 45, 25))
ST = Sess0.Screen.GetString(6, 77, 2)
ZP = Sess0.Screen.GetString(7, 6, 5)
rw1 = rw1 + 1
Sheets("Workbook").Cells(rw1, 1) = CONT
Sheets("Workbook").Cells(rw1, 2) = ID
Sheets("Workbook").Cells(rw1, 3) = ENTITY
Sheets("Workbook").Cells(rw1, 4) = PRDGRP
Sheets("Workbook").Cells(rw1, 5) = Date1
Sheets("Workbook").Cells(rw1, 6) = Date2
Sheets("Workbook").Cells(rw1, 7) = MCode
Sheets("Workbook").Cells(rw1, 8) = ISB
Sheets("Workbook").Cells(rw1, 9) = FR
Sheets("Workbook").Cells(rw1, 10) = MA
Sheets("Workbook").Cells(rw1, 11) = LA
Sheets("Workbook").Cells(rw1, 12) = SN
Sheets("Workbook").Cells(rw1, 13) = SR
Sheets("Workbook").Cells(rw1, 14) = CT
Sheets("Workbook").Cells(rw1, 15) = ST
Sheets("Workbook").Cells(rw1, 16) = ZP

End If

'Return to records screen
Sess0.Screen.SendKeys ("<Pf3>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop
'Exit Do

End If

Next r

'Next page
Sess0.Screen.SendKeys ("<Pf8>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop

Loop

Sess0.Screen.SendKeys ("<Pf3>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop

Sess0.Screen.SendKeys ("<Pf3>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop

Sess0.Screen.SendKeys ("<Pf3>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop

Next X 'next row/group
Loop
End With





End Sub

 

[CADD6953]

Alrighty, I've been making some edits to both macros (I went ahead and just updated it within the last post since these are fairly long macros and a bit bothersome to repost with minor changes). I realized the PG1-PG5 values I had being referenced from my excel cells of the first screen scrape would not be consistently 14 rows apart every time when screen scraping the records of multiple inputs and that I would need more than 5 for multiple inputs I'm trying to collect data for so I've had to resort to indexing the page the data is pulled in from via the first macro (listed below). This isn't as great because it's not checking any data on the pages before proceeding into each record but I'll have to brainstorm on how to add a validation in once I get it all working.

                                    Sheets("Workbook").Cells(rw1, 1) = rw2

While amending the second macro I'm now getting a compile error of 'Next without For' on the last Next syntax. Any ideas what I may not have closed out and what I'm missing? I've tried another End If above this but that will give another compile error of 'End If without block If' and any I try to remove give errors up to those points in the coding.

            Next X  'next row/group '2

 

[SkipVought]

I think that now is the time to look at streamlining your code.

I'm sure you've noticed that you have lots of repeated code. What sticks out the most is where you repeat PF8 one after the other. This is part of your screen navigation. These can probably be in a loop. Since I don't have access to your emulator, I don't know the logic of x repeats of PF8 or any other repeated command.

Then you have repeated use of scrape-and-store code. Those could be put in a separate procedure using module-level declarations for each variable used.

This will greatly simplify and minimize your code.

I'd like to see a pic of each screen and your "workbook" worksheet along with any explanation that would help me understand the screen navigation.

I didn't have the time today to scour your code to find the missing For.

Totally don't know what you mean by, "I realized the PG1-PG5 values..." and so on.

You have to reveal a LOT more about your screen navigation before I can be of specific help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[CADD6953]

I've attached a word document with a high level walk through/key strokes on each screen (redacted due to sensitive information and page 5 is not included as it was too much to redact and is really only needed for screen scraping).

I've set the variables and inserted a loop for the PF8 keys which I think will work but I'll need to wait to test it as I am still encountering the For error and I cannot figure out where I've messed up the syntax.

 

[SkipVought]

Some comments on your code.

You have declared a workbook object and a worksheet object. But you fail to use them.

In this case, where you only reference ONE workbook and ONE worksheet, declaring object variables is really unnecessary. But lets go ahead and use them. I'll make a few minor changes to illustrate what I like to do. And I'm leaving out some code simply to emphasize these things.

Sub RecordLookUp2()
   Dim wbMain As Workbook, wsOutData As Worksheet
   Dim iScrn as Integer

   Set wbMain = ActiveWorkbook
   Set wsOutData = wbMain.WorkSheets("Workbook")

   rw = 2
   rw1 = 1
   rw2 = 2

   With wsOutData
      Do '1
            
         For X = rw To .Rows.Count '2
            PO = Left(.Cells(X, 2).Value & "000000000", 9)
            If PO = "000000000" Then Exit Sub
'Screens 1 & 2
            For iScrn = 1 To 2  
               Sess0.Screen.PutString "BA", 5, 22
               Sess0.Screen.SendKeys ("<Enter>")
               Do While Sess0.Screen.OIA.XStatus <> 0
                  DoEvents
               Loop
             Next
'Screen 3
             Sess0.Screen.PutString PO, 3, 44
             Sess0.Screen.PutString "CD", 5, 22
             Sess0.Screen.SendKeys ("<Enter>")   
             Do While Sess0.Screen.OIA.XStatus <> 0
                DoEvents
             Loop
'Screen 4
             SB = Trim(Sess0.Screen.GetString(6, 2, 3))
             If SB = "SUB" Then '3
'.....
             Else '3
                Sess0.Screen.SendKeys ("<Pf2>")
                Do While Sess0.Screen.OIA.XStatus <> 0
                   DoEvents
                Loop
             End If '3
         Next X '2
      Loop '1
   End With
End Sub

Now here, starting sheet 4 I do not see "SUB" @ 6,2???

NOTICE:
1) when you use With..End With, the Reference, in this case the worksheet object, wsOutData, is implied by each DOT Reference (.Rows.Count & .Cells(X, 2).Value) to wsOutData within the block.
2) I'm using PutString to place data on the screen. Neither is a command sending data to the mainframe UNTIL you hit SendKeys "ENTER"
3) I've put in ALL of the Block Format at the appropriate indent. I make a practice of doing this each time I add a block so I greatly minimize having a For without Next or If without End If.

Sub...End Sub
With...End With
Do...Loop
If...Else...End If
For...Next

BTW, the Do...Loop followed by For...Next is unnecessary.
To exit a For...Next before the limit use an If test to Exit For.

For i = A To B
'....
   If [i]expression[/i] Then Exit For
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[CADD6953]

Skip, you saint! I got it working and tested it out with about 60+ initial inputs that scraped over 900 lines of data into excel - couldn't find any issues.

I did have to make quite a few amendments to the code though. At the start of each new input search, it's running a loop of PF7 10 times in the Attachmate session because for whatever reason if I used PF7 only once or twice sometimes it would go back to the list of records for the previous input while other times it would somewhat get lost in the paging of the current record. This loop ensures it always resets to the first page of that input's records (barring any records with 10+ pages it might previously pull up). There was no key(s) to reset the screen and remove that previous search history without having to restart the session itself so I figured this was the quicker option although I would have liked it to work off of a loop based on the PG2 variable to page back the appropriate number of times when necessary. I did in fact try this and ran into the same issue as stated earlier where it goes into the wrong record or gets lost in the paging of the correct record.

"SUB" at 6,2 is correct, I edited this area in the screenshot due to some sensitive information.

I removed the second Do...Loop to give that a shot but it backs out to the first screen every time and then got a bit lost after that so I figured I'd better not fix something that's not broke for me right now.

Embedding the screen scrape macro in it's own sub and calling that into the main sub was a genius idea though so thanks so much for that! If you see any other places where I might be able to clean up feel free to let me know!

Option Explicit
    Dim Sessions, System As Object, Sess0 As Object
    Dim wbMain As Workbook, wsOutData As Worksheet
'Main Sub
    Dim X As Long, PG1 As Long
    Dim iScrn As Integer, rw As Integer, rw1 As Integer, SB As String, r As Integer, dd As Integer, PG2 As Integer, p As Integer, p2 As Integer
    Dim PO As String, PG As String, ID As String, S As String, I As String, ML As String, CD As String
'Scrub Subs
    Dim CONT As String, ENTITY As String, PRDGRP As String, MA As String, LA As String, SN As String, SR As String, CT As String, ST As String, ZP As String

Sub RecordLookUp2()
    Set System = CreateObject("EXTRA.System")
    Set Sessions = System.Sessions
    Set Sess0 = System.ActiveSession
    Set wbMain = ActiveWorkbook
    Set wsOutData = wbMain.Worksheets("Workbook")
    
    rw = 2
    rw1 = 1
        
    With wsOutData
        Do '1.1
            For X = rw To .Rows.Count '1.2
                PO = Left(.Cells(rw, 3).Value & "000000000", 9)
                If PO = "000000000" Then Exit Sub

'Screens 1 & 2
                For iScrn = 1 To 2 '2.1
                    Sess0.Screen.PutString "BA", 5, 22
                    Sess0.Screen.SendKeys ("<Enter>")
                    Do While Sess0.Screen.OIA.XStatus <> 0
                        DoEvents
                    Loop
                Next

'Screen 3
                Sess0.Screen.PutString PO, 3, 44
                Sess0.Screen.PutString "CD", 5, 22
                Sess0.Screen.SendKeys ("<Enter>")
                Do While Sess0.Screen.OIA.XStatus <> 0
                    DoEvents
                Loop

'Screen 4 Opt 1
                PG = Left("0" & .Cells(rw, 1).Value & "0", 2)
                ID = Sess0.Screen.GetString(3, 44, 9)
                p = 1
                
                For p = 1 To 10 '4.1
                    Sess0.Screen.SendKeys ("<Pf7>")
                    Do While Sess0.Screen.OIA.XStatus <> 0
                        DoEvents
                    Loop
                Next p '4.1
                
                SB = Trim(Sess0.Screen.GetString(6, 2, 3))
                If SB = "SUB" Then '4.2

                    Do '4.3
                        For r = 8 To 21 '4.4
                            dd = 1
                            PO = Left(.Cells(rw, 3).Value & "000000000", 9)
                            PG = Left("0" & .Cells(rw, 1).Value & "0", 2)
                            ID = Sess0.Screen.GetString(3, 44, 9)
                                    
                            If PG = "01" And PO = ID Then '4.5
                                S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                        
                                If S = "" And I = "" Then '4.6
                                Exit Do

'Screen 5
                                Else '4.6
                                    Sess0.Screen.PutString I, 5, 18
                                    Sess0.Screen.SendKeys ("<Enter>")
                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                        DoEvents
                                    Loop
    
                                    ML = Trim(Sess0.Screen.GetString(2, 23, 27))
                                    If ML = "INQUIRY" Then '4.7
                                        RunScrape1
                                                
                                    Else '4.7
                                        RunScrape2

                                    End If '4.7

                                Sess0.Screen.SendKeys ("<Pf3>")
                                Do While Sess0.Screen.OIA.XStatus <> 0
                                    DoEvents
                                Loop
                                            
                                rw = rw + 1
                                
                                End If '4.6
                                    
'Screen 4 Opt2
                            ElseIf PG > "01" And PO = ID Then '4.5
                                PG1 = .Cells(rw, 1).Value
                                PG2 = PG1 - 1
                                p = 1

                                For p = 1 To PG2 '4.6
                                    Sess0.Screen.SendKeys ("<Pf7>")
                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                        DoEvents
                                    Loop
                                Next p '4.6
                                        
                                For p2 = 1 To PG2 '4.6
                                    Sess0.Screen.SendKeys ("<Pf8>")
                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                        DoEvents
                                    Loop
                                Next p2 '4.6
                
                                S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                I = Trim(Sess0.Screen.GetString(r, 2, 2))
'Screen 5
                                If S = "" And I = "" Then '4.6
                                Exit Do
                                        
                                Else '4.6
                                    Sess0.Screen.PutString I, 5, 18
                                    Sess0.Screen.SendKeys ("<Enter>")
                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                        DoEvents
                                    Loop
                                                
                                    ML = Trim(Sess0.Screen.GetString(2, 23, 27))
                                    If ML = "INQUIRY" Then '4.7
                                        RunScrape1

                                    Else '4.7
                                        RunScrape2

                                    End If '4.7

                                Sess0.Screen.SendKeys ("<Pf3>")
                                Do While Sess0.Screen.OIA.XStatus <> 0
                                    DoEvents
                                Loop
                                            
                                CD = Trim(Sess0.Screen.GetString(8, 56, 2))
                                    If CD = "CD" Then '4.7
                                        Sess0.Screen.PutString "CD", 5, 22
                                        Sess0.Screen.SendKeys ("<Enter>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                                
                                    Else '4.7
                                    End If
                                                
                                rw = rw + 1
                                                                                               
                                End If '4.6
                                    
                            Else '4.5
                            Exit Do
                                    
                            End If '4.5
        
                                
                        Next r '4.4
                                                               
                    Loop '4.3
                            
                Else '4.2
                    Sess0.Screen.SendKeys ("<Pf2>")
                    Do While Sess0.Screen.OIA.XStatus <> 0
                        DoEvents
                    Loop
                End If '4.2
                         
                Sess0.Screen.SendKeys ("<Pf2>")
                Do While Sess0.Screen.OIA.XStatus <> 0
                    DoEvents
                Loop
                    
            Next X  'next row/group '1.2
        Loop '1.1
    End With
End Sub

Public Sub RunScrape1()
    Set System = CreateObject("EXTRA.System")
    Set Sessions = System.Sessions
    Set Sess0 = System.ActiveSession
    Set wbMain = ActiveWorkbook
    Set wsOutData = wbMain.Worksheets("Workbook")
        
    With wsOutData
        CONT = Sess0.Screen.GetString(4, 28, 4)
        ENTITY = Sess0.Screen.GetString(4, 9, 6)
        PRDGRP = Sess0.Screen.GetString(4, 28, 6)
        MA = Sess0.Screen.GetString(5, 69, 1)
        LA = Trim(Sess0.Screen.GetString(5, 12, 27))
        SN = Sess0.Screen.GetString(8, 23, 9)
        SR = Trim(Sess0.Screen.GetString(6, 14, 25))
        CT = Trim(Sess0.Screen.GetString(6, 45, 25))
        ST = Sess0.Screen.GetString(6, 78, 2)
        ZP = Sess0.Screen.GetString(7, 6, 5)
        rw1 = rw1 + 1
        .Cells(rw1, 2) = CONT
        .Cells(rw1, 4) = ENTITY
        .Cells(rw1, 5) = PRDGRP
        .Cells(rw1, 11) = MA
        .Cells(rw1, 12) = LA
        .Cells(rw1, 13) = SN
        .Cells(rw1, 14) = SR
        .Cells(rw1, 15) = CT
        .Cells(rw1, 16) = ST
        .Cells(rw1, 17) = ZP
    End With
End Sub

Public Sub RunScrape2()
    Set System = CreateObject("EXTRA.System")
    Set Sessions = System.Sessions
    Set Sess0 = System.ActiveSession
    Set wbMain = ActiveWorkbook
    Set wsOutData = wbMain.Worksheets("Workbook")
        
    With wsOutData
        CONT = Sess0.Screen.GetString(8, 18, 4)
        ENTITY = Sess0.Screen.GetString(4, 9, 6)
        PRDGRP = Sess0.Screen.GetString(4, 28, 6)
        MA = Sess0.Screen.GetString(5, 69, 1)
        LA = Trim(Sess0.Screen.GetString(5, 12, 27))
        SN = Sess0.Screen.GetString(9, 72, 9)
        SR = Trim(Sess0.Screen.GetString(6, 14, 25))
        CT = Trim(Sess0.Screen.GetString(6, 45, 25))
        ST = Sess0.Screen.GetString(6, 77, 2)
        ZP = Sess0.Screen.GetString(7, 6, 5)
        rw1 = rw1 + 1
        .Cells(rw1, 2) = CONT
        .Cells(rw1, 4) = ENTITY
        .Cells(rw1, 5) = PRDGRP
        .Cells(rw1, 11) = MA
        .Cells(rw1, 12) = LA
        .Cells(rw1, 13) = SN
        .Cells(rw1, 14) = SR
        .Cells(rw1, 15) = CT
        .Cells(rw1, 16) = ST
        .Cells(rw1, 17) = ZP
    End With
End Sub

 

[SkipVought]

Good for you! Like a wild horse, you have taken the bit in your teeth and run like the wind!

Here's another tip, like polishing an apple and tuning the string to a perfect harmonic. It could be a constant labor of love.

Look for places where an object variable can save you multiple dot levels of reference. Here oScreen can replace Sess0.Screen.

Option Explicit
    Dim Sessions, System As Object, Sess0 As Object, oScreen As Object
'...
Sub RecordLookUp2()
    Set oScreen = Sess0.Screen
'...
    With oScreen
       .PutString "BA", 5, 22
'...
    End With
'...
End Sub

Public Sub RunScrape2()
'These are Module level variables that were Set in the calling procedure.
    [s]Set System = CreateObject("EXTRA.System")
    Set Sessions = System.Sessions
    Set Sess0 = System.ActiveSession
    Set wbMain = ActiveWorkbook
    Set wsOutData = wbMain.Worksheets("Workbook")[/s]
        
    With wsOutData
        With oScreen
           CONT = .GetString(8, 18, 4)
           ENTITY = .GetString(4, 9, 6)
           PRDGRP = .GetString(4, 28, 6)
           MA = .GetString(5, 69, 1)
           LA = Trim(.GetString(5, 12, 27))
           SN = .GetString(9, 72, 9)
           SR = Trim(.GetString(6, 14, 25))
           CT = Trim(.GetString(6, 45, 25))
           ST = .GetString(6, 77, 2)
           ZP = .GetString(7, 6, 5)
        End With
        rw1 = rw1 + 1
        .Cells(rw1, 2) = CONT
        .Cells(rw1, 4) = ENTITY
        .Cells(rw1, 5) = PRDGRP
        .Cells(rw1, 11) = MA
        .Cells(rw1, 12) = LA
        .Cells(rw1, 13) = SN
        .Cells(rw1, 14) = SR
        .Cells(rw1, 15) = CT
        .Cells(rw1, 16) = ST
        .Cells(rw1, 17) = ZP
    End With
End Sub

These two FAQs ought to be helpful.
faq707-4105
Faq707-4594

FYI: Most of my posting regarding the use of VBA code is in forum707.

It is just the honest truth that I found VBA and the Editor, were so much more superior to Extra VB and that Editor that I never wasted my time trying to learn the lesser of the two when I was ALWAYS using an Excel list to drive any process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Here's a possible next step in the evolution of your procedure and your programming progress.

The stated goal is to remove as much DATA embedded in your code as possible and rather embed the data in one or more tables. Managing dozens of sets of values in a table used by one set of code is much easier than managing dozens of sets of code.

So the specific goal is to place the screen data in a table with headings like:
[pre]
Screen
Field
Start Row
End Row
Column
Length
Type (Num or Chr)
[/pre]

So in this case, making some assumptions...
[pre]
Screen Field Start End COL Length Type
Screen4 SEL 8 21 2 2 NUM
Screen4 CONT 8 21 6 4 CHR
Screen4 ENTITY 8 21 11 6 CHR
Screen4 PRDGRP 8 21 18 6 CHR
Screen4 DATE1 8 21 25 8 CHR
Screen4 DATE2 8 21 34 8 CHR
Screen4 MB 8 21 43 2 CHR
Screen4 ISB 8 21 46 8 CHR
Screen4 FA 8 21 55 3 CHR
Screen4 MGS 22 22 2 79 CHR
[/pre]

Now I did this for every every single field on the screen and I had dozens of screens so defined in my table. And my corresponding worksheet had the same headings in the same order. So I would read down the table for the given screen and do a GetString according to the values in the table then a PutString in my worksheet.

On screens where there were a range of rows, all the single-row fields would be repeated for each row. The Type field was to either make the Excel column GENERAL for NUM or TEXT for CHR. If the leading ZERO is necessary, then TEXT/CHR.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!