Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help needed with syntax/logic in Attachmate macro

Status
Not open for further replies.
CADD6953

CADD6953

Technical User
Dec 22, 2018
31
US
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
 
Sort by date Sort by votes
Hi there, duckin' the wind sweepin cross the plane, 🥴

Worked at a place that had DOZENS of screen-scrape macros, like your situation, in Excel. I started getting folks wanting selected data abc, from screen xyz, given list mno, thus generating macro qrs.

After a half-dozen of these requests, I wondered if I could make a generalized macro, where any complete screen could be specified by DATA, and the screen data output to a specific worksheet in the screen-scrape workbook given an input sheet specifying the SCREEN and the INPUT DATA TABLE.

It's much simpler to pick data columns in Excel to report than modifying an existing macro to define columns and rows to scrape.

Unfortunately, I've lost my MASTER.xlsm workbook.

That being said, you mention PF3 not getting you a second page. Did you mean PF8?
Okay I see you are using PF3 on subsequent pages. How does it work manually?

What method do you use manually? It is the manual process that the screen-navigation-code patterns.

You need some sort of indication when the paging complete. In my experience, the screens were designed with a MESSAGE area on the last row (usually row24 as I recall) and I accumulated a full list of all messages and subsequently coded as required by the message.

It all starts with a knowledge of these factors.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
...and BTW the screen format table that I used is described here:
faq99-7950

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
Heya Skip!

PF8 is like the next or next page function while PF3 is like the back function.

The manual process of it is that you would enter an input (designated by PO in this case) then hit enter to see a list of all records for that value; if there are more records that that screen can show then it will have multiple pages/screens you can tab through using PF8. This list view gives high level information of each record which isn't very helpful for what is needed in the extract. However you can also look at each record in more detail by using the selection code (designated I in this case) which will take you into a different screen with more data fields that are helpful but the only way to get back to the complete listing of records is by using the PF3 key which unfortunately takes you back to the first page of records (so if there are multiple pages of records this is where I'm running into the issue). For this macro it can go into that more detailed screen for any of the records on the first page of the list and retrieve the necessary information for each while going back to the listing without any issue but pages 2, 3, 4, and so on have the issue.

As an example, let's say records/rows 1 & 8 on page 1 of the listing have the null value for 'S' so the macro is recognizing it needs to go into the more detailed screen of the record and retrieve that information - no issues. The macro continues through all the records/rows on page 1 then moves to page 2 where you might see rows 5 & 6 have null values for 'S' - it will go into the detailed view of record 5 and pull that data just fine but the PF3 key takes you back to page 1 of 2 in the records list where it will then continue with the logic and end up recapturing the details from record 7 again on page 1. I need it to recognize that it left off on page 2 and so when it uses that PF3/back feature it automatically uses the PF8 key the necessary number of times to get back to the page it left off on (i.e. PF8 x1 if on page 2, PF8x2 if on page 3, etc.).

I do see that Page 1 has a message on line 22 essentially stating more data is available, meaning that another page of records can be tabbed to using PF8 but I don't think that using this identifier would help much as there could be multiple pages of data for one input so I'm not sure how the macro would recognize exactly which page it had left off on. It's kind of like forgetting your place in a book and re-reading portions of it except it never gets past a certain point (sounds a little like me reading some of the textbooks in college).
 
Upvote 0 Downvote
Do PA1, PA2 or PA3 key recover the previous page place?

Otherwise, you need to store a value the representing the Page Number and maybe even the row number that you last interrogated.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
Unfortunately the PA1 key takes you back to a screen to select a session, the PA2 key would be to move to the next session and the PA3 key seems to have no effect on the screen.

PF3 is the only one I see notated as a return function but that starts you back off at the first page/entries of whatever the previous screen was.
 
Upvote 0 Downvote
Then the Otherwise I posted might be appropriate, to be able to navigate back to the previous page and row last traversed.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
I agree that the alternative is the proper route if this is to be functional, I'm just not too skilled or versed in coding to know how to best write that. The values in the records/rows I'm looking for change between pages so I would assume I'd want to use a value there for the macro to reference in determining whether it's on the right page in addition to an If/Else statement early in the 'For r = 8 To 21' section of the code. For example, have it store maybe the value in location (8, 5, 4) and then reference prior to executing the search for 'S' being null in each row/record that way if the value matches or remains the same it can proceed onto the next record else it should PF8 and see if the value in that location on the next page matches what was stored.
 
Upvote 0 Downvote
One of the first things you need to correctly define/design and correctly code screen navigation before you begin coding. That's got to work perfectly!

Then you can address screen-scraping design & code.

I can give you tips on specific questions, but I don't have the time to devote to examining your code and giving your feedback there. As I see things, I'll comment.

I'm hampered by the fact that I haven't coded a screen-scrape for close to a decade. I'm relying on what I can remember about 3270 terminal emulation. Your emulator might be different. And I have no access to Attachmate!Extra. Just what I can find by Googling. I'll do what I can for you.


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
Okay, so I tried adding in reference values PR/PG. I'm not sure if this would be the correct/best way to have a reference as it doesn't seem like the macro is seeing that the values match and is thus resorting to the Else '5 command sequence. I can see it's storing the correct value from Attachmate into Excel but not sure whether the PG = PR step is making sense or being accomplished. Ideas? I think the order of commands otherwise is all good and the screen scraping would be no issue...just need it to recognize the reference cell and actually compare it with the value in Attachmate. Apologies again as the code feature is giving me errors so it won't allow the below to be put in properly with all the indentations

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 '1
For X = rw To ActiveSheet.Rows.Count '2
PO = Left(Sheets("Look-up").Cells(X, 1).Value & "000000000", 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
Sess0.Screen.MoveTo 3, 44
Sess0.Screen.SendKeys PO
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop


PG = Left(Sheets("Look-up").Cells(1, 2).Value & "0000", 4)
If PG = "0000" Then Exit Sub
PR = Sess0.Screen.GetString(8, 5, 4)
Sheets("Look-up").Cells(1, 2) = PR
Do '3
For r = 8 To 21 '4
dd = 1
S = Trim(Sess0.Screen.GetString(r, 33, 8))
I = Trim(Sess0.Screen.GetString(r, 2, 2))

If PG = PR Then '5

If S = "" And I = "" Then
Exit Do '6

ElseIf S = "" Then '6
Sess0.Screen.MoveTo 5, 18
Sess0.Screen.SendKeys I
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 '7
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(8, 52, 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 '7
CONT = Sess0.Screen.GetString(8, 18, 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 '7



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

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

If S = "" And I = "" Then
Exit Do '8

ElseIf S = "" Then '8
Sess0.Screen.MoveTo 5, 18
Sess0.Screen.SendKeys I
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 '9
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(8, 52, 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 '9
CONT = Sess0.Screen.GetString(8, 18, 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 '9

Sess0.Screen.SendKeys ("<Pf3>")
Do While Sess0.Screen.OIA.XStatus <> 0
DoEvents
Loop
End If '8
End If '5

Next r '4

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

PR = Sess0.Screen.GetString(8, 5, 4)
Sheets("Look-up").Cells(1, 2) = PR

Loop '3

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


Next X 'next row/group '2
Loop '1
End With





End Sub
 
Upvote 0 Downvote
Also, based on your advice I tried without the screen scraping to see how it functions and only saw how the page navigation & other commands executed. It seems to be working great and recognizing the reference values then navigating to the correct screen based off that. However, when following the Else 5' command sequence it's not executing the S = "" Then command correctly and is just going through all the rows as well rather than just selecting the ones with a null value.
 
Upvote 0 Downvote
Regarding PG/PR:
Code: 
PG = Left(Sheets("Look-up").Cells(1, 2).Value & "0000", 4)
If PG = "0000" Then Exit Sub
PR = Sess0.Screen.GetString(8, 5, 4)
Sheets("Look-up").Cells(1, 2) = PR
What is in [tt]Sheets("Look-up").Cells(1, 2).Value[/tt] initially?
I can see that you're using this cell to keep track of where you are in the sheet. Maybe your code needs to grab the initial value from the sheet before you begin you loop through that screen.

Regarding the CODE feature here, I rarely use the buttons, but if you do, you must FIRST SELECT the text you want in a CODE Window and then hit the CODE button. Otherwise, as I mostly do, put the TGML tag in yourself...
[ignore]
Code: 
[/ignore]

[ignore]
[/ignore]
...and then paste your procedure in between.

Once you get your paging working, i'll give you a few more ideas.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
Okay, made some changes which seemed to have fixed the macro recognizing and using the reference value correctly so that is no longer the issue. The PG value is null starting off in excel until stored with the PR value from Attachmate. This value will be overwritten when the macro goes through the For statement the first time and then pages to the second page of records

Code: 
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 '1
             For X = rw To ActiveSheet.Rows.Count '2
                PO = Left(Sheets("Look-up").Cells(X, 1).Value & "000000000", 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
                Sess0.Screen.MoveTo 3, 44
                Sess0.Screen.SendKeys PO
                Sess0.Screen.SendKeys ("<Enter>")
                Do While Sess0.Screen.OIA.XStatus <> 0
                    DoEvents
                Loop
                
                
                PG = Left(Sheets("Look-up").Cells(1, 2).Value & "000000000", 4)
                If PG = "000000000" Then Exit Sub
                PR = Sess0.Screen.GetString(8, 5, 4)
                Sheets("Look-up").Cells(1, 2) = PR
                
                If PG = PR Then
                
                    Do '3
                        For r = 8 To 21 '4
                        dd = 1
                        'S = Trim(Sess0.Screen.GetString(r, 33, 8))
                        'I = Trim(Sess0.Screen.GetString(r, 2, 2))
                        
                            If PG = PR Then '5
                                S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                
                                If S = "" And I = "" Then
                                Exit Do '6
                            
                                ElseIf S = "" Then '6
                                Sess0.Screen.MoveTo 5, 18
                                Sess0.Screen.SendKeys I
                                Sess0.Screen.SendKeys ("<Enter>")
                                Do While Sess0.Screen.OIA.XStatus <> 0
                                    DoEvents
                                Loop

                                    'Screen Scrape coding will be placed here
                                

                                    Sess0.Screen.SendKeys ("<Pf3>")
                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                        DoEvents
                                    Loop
                                End If '6
                            
                            Else '5
                            Sess0.Screen.SendKeys ("<Pf8>")
                            Do While Sess0.Screen.OIA.XStatus <> 0
                                DoEvents
                            Loop
                                'If PG = PR Then '5
        
                                S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                
                                If S = "" And I = "" Then
                                Exit Do '8
                            
                                ElseIf S = "" Then '8
                                Sess0.Screen.MoveTo 5, 18
                                Sess0.Screen.SendKeys I
                                Sess0.Screen.SendKeys ("<Enter>")
                                Do While Sess0.Screen.OIA.XStatus <> 0
                                    DoEvents
                                Loop
                                    
                                    'Screen Scrape coding will be placed here
                                    
                                    Sess0.Screen.SendKeys ("<Pf3>")
                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                        DoEvents
                                    Loop
                                    End If '9
                                End If '8
                            'End If '5

                        Next r '4
    
                        Sess0.Screen.SendKeys ("<Pf8>")
                        Do While Sess0.Screen.OIA.XStatus <> 0
                            DoEvents
                        Loop
                        
                        PR = Sess0.Screen.GetString(8, 5, 4)
                        Sheets("Look-up").Cells(1, 2) = PR
                        Do While Sess0.Screen.OIA.XStatus <> 0
                            DoEvents
                        Loop
                        
                        Sess0.Screen.SendKeys ("<Pf3>")
                        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

                    Loop '3
                    
                Sess0.Screen.SendKeys ("<Pf2>")
                Do While Sess0.Screen.OIA.XStatus <> 0
                    DoEvents
                Loop
               Else: End If
               
    
             Next X  'next row/group '2
            Loop '1
        
        End With





End Sub

Now the issue is that the macro will use the reference value correctly, move to the Else '5 sequence correctly but will think that both S and I are null (when in fact they are not) which is causing it to exit the Do cycle. It shows that the r value is equal to the correct row it should read on that second page (r = 8) so I'm wondering if this needs to also be specified outside the loop.

Code: 
                            Else '5
                            Sess0.Screen.SendKeys ("<Pf8>")
                            Do While Sess0.Screen.OIA.XStatus <> 0
                                DoEvents
                            Loop
                                'If PG = PR Then '5
        
                                S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                
                                If S = "" And I = "" Then
                                Exit Do '8
 
Upvote 0 Downvote
I notice that
Code: 
'If PG = PR Then '5
...is a COMMENT.

But what you can do is
1) UNCOMMENT that statement
2) Put a BREAK on that statement
3) Run to the BREAK, at which time, use the Watch Window to inspect the values stored in each if your variables.
faq707-4594

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
Alrighty, it looks like the value is being scraped and stored into excel in the cell that PG is referencing however the comment of PG = PR is showing that the value for PG is 0000 so there is a problem. PR is storing the correct value. If I tried to run the macro a second time, while that value is already stored then the statement is true in that PG and PR are equal and showing the same value. How might I be able to have it store and return that same value without needing to restart the macro or creating another with statement?

If I continue running the macro, everything else runs fine until it gets to page 2 where it will successfully screen scrape the new value for PG from Attachmate but yet again will get to the PG = PR statement and will show PG as the old value rather than registering what it stored in excel just before.

Basically I think whatever the value is at the start of the macro in the cell PG is linked to is what that value remains throughout the macro until I restart the macro with a new value in that cell. For example, first run of the macro PG is initially 0000, it will screen scrape and store 1234 but still reads it as 0000 so the macro ends. Second run of the macro it will read the 1234 stored in that cell now so PG = 1234, it will then see that matches all records on page one and executes the commands for that page correctly, then tabs to the next page of records and stores the new value 5678 and tries to loop - during the loop it still sees PG as equal to 1234 so it's continuously looping the pulls from page 1.
 
Upvote 0 Downvote
The problem is that you must store the PREVIOUS page number just before you PF8 to the next page.
Then PF8
Then store the CURRENT page number.

Notice that the FIRST page of any screen is a unique condition, that has no PREVIOUS value.

All this is part of screen navigation, including interrogating the message area after any codes that you send to the mainframe and performing appropriate steps. I notice that you are using a Do...Loop waiting for the mainframe to be ready to process any command. That's very astute.👍

Once you get your screen navigation code figured out, we might attempt modifying your code to simplify and streamline, if you care to do that.

Almost all of the screens that my users asked me to scrape were a one-screen process. So my paging was simpler. On very few occasions I encountered multiple screen situations and I can't remember any specifics from those encounters. I tend to think that the coding for those applications were all unique or COPY similar code and modify.

I kick myself for having lost my master workbook.

Here's a thought I just had.

In your process, you go to another screen based on values in the first screen. So what if you processed each screen separately, generating a list/table from the first screen and then processing that list/table to scrape the second screen?

Just a thought

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
Alrighty Skip :) I've got it working now but had to break it apart into 2 macros - the first one to screen scrape the records pages and acquire some reference values and the second to go into the more detailed pages based off the reference values (I don't think there should ever be more than 5 pages of records per initial input ID from the first macro). For the second macro I had to introduce some PF7 syntax as I noticed sometimes it was not starting on the first page (odd but saw it occur quite a few times) so this is like the 'Last page' function and that ensures it's always at the true first page before continuing.

Macro 1:
Code: 
Sub RecordLookUp1()
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
        rw2 = 1
        With Worksheets("Look-up")
            Do
             For X = rw To ActiveSheet.Rows.Count
                PO = Left(Sheets("Look-up").Cells(X, 1).Value & "000000000", 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
                Sess0.Screen.MoveTo 3, 44
                Sess0.Screen.SendKeys PO
                Sess0.Screen.SendKeys ("<Enter>")
                Do While Sess0.Screen.OIA.XStatus <> 0
                    DoEvents
                Loop
                
                    
                SB = Trim(Sess0.Screen.GetString(6, 2, 3))
                If SB = "SUB" Then
                
                    Do
                        For r = 8 To 21
                        dd = 1
                            S = Trim(Sess0.Screen.GetString(r, 33, 8))
                            I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                    
                                If S = "" And I = "" Then
                                Exit Do
                                
                                Else
                                    CONT = Sess0.Screen.GetString(r, 5, 4)
                                    ID = Sess0.Screen.GetString(3, 44, 9)
                                    ENTITY = Sess0.Screen.GetString(r, 10, 6)
                                    PRDGRP = Sess0.Screen.GetString(r, 17, 6)
                                    Date1 = Sess0.Screen.GetString(r, 24, 8)
                                    Date2 = Sess0.Screen.GetString(r, 33, 8)
                                    MCode = Sess0.Screen.GetString(r, 42, 2)
                                    ISB = Sess0.Screen.GetString(r, 45, 8)
                                    FR = Trim(Sess0.Screen.GetString(r, 54, 12))
                                    rw1 = rw1 + 1
                                    Sheets("Workbook").Cells(rw1, 1) = rw2
                                    Sheets("Workbook").Cells(rw1, 2) = CONT
                                    Sheets("Workbook").Cells(rw1, 3) = ID
                                    Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                    Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                    Sheets("Workbook").Cells(rw1, 6) = Date1
                                    Sheets("Workbook").Cells(rw1, 7) = Date2
                                    Sheets("Workbook").Cells(rw1, 8) = MCode
                                    Sheets("Workbook").Cells(rw1, 9) = ISB
                                    Sheets("Workbook").Cells(rw1, 10) = FR
                                        
                                End If
                                    
                            Next r
        
                            Sess0.Screen.SendKeys ("<Pf8>")
                            Do While Sess0.Screen.OIA.XStatus <> 0
                                DoEvents
                            Loop
                            
                            rw2 = rw2 + 1
                            
                    Loop
                        
                    Sess0.Screen.SendKeys ("<Pf2>")
                    Do While Sess0.Screen.OIA.XStatus <> 0
                        DoEvents
                    Loop
                    
                Else
                End If
                
                rw2 = 1
                
             Next X  'next row/group '
            
            Loop
        
        End With





End Sub

Macro 2:
Code: 
Sub RecordLookUp2()
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("Workbook")

        rw = 2
        rw1 = 1
        rw2 = 2
        With Worksheets("Workbook")
            Do '1
            
                For X = rw To ActiveSheet.Rows.Count '2
                    PO = Left(Sheets("Workbook").Cells(X, 2).Value & "000000000", 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 3, 44
                    Sess0.Screen.SendKeys PO
                    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
                    
                    SB = Trim(Sess0.Screen.GetString(6, 2, 3))
                    If SB = "SUB" Then '3
                        
                        PG = Left(Sheets("Workbook").Cells(X, 1).Value & "00", 2)
                    
                        Do '4
                                For r = 8 To 21 '5
                                dd = 1
                    
                                    If PG = "01" Then '6
                                        rw2 = rw2 + 1
                                        
                                        Sess0.Screen.SendKeys ("<Pf7>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                        I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                        
                                        If S = "" And I = "" Then '7
                                        Exit Do
                                    
                                        Else '7
                                        Sess0.Screen.MoveTo 5, 18
                                        Sess0.Screen.SendKeys I
                                        Sess0.Screen.SendKeys ("<Enter>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
        
                                            'Screen Scrape coding will be placed here
                                            ML = Trim(Sess0.Screen.GetString(2, 23, 27))
                                            If ML = "MEMBER LIFE PROFILE INQUIRY" Then '8
                                                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))
                                                CR = 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, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                            Else '8
                                                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
                                                Sheets("Workbook").Cells(rw1, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                            
                                            End If '8
                                        
        
                                            Sess0.Screen.SendKeys ("<Pf3>")
                                            Do While Sess0.Screen.OIA.XStatus <> 0
                                                DoEvents
                                            Loop
                                            
                                        End If '7
                                    'End If '8
                                    
                                    ElseIf PG = "02" Then '6
                                        
                                        rw2 = rw2 + 1
                                    
                                        Sess0.Screen.SendKeys ("<Pf7>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                
                                            S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                            I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                            
                                            If S = "" And I = "" Then '7
                                            Exit Do
                                        
                                            Else '7
                                            Sess0.Screen.MoveTo 5, 18
                                            Sess0.Screen.SendKeys I
                                            Sess0.Screen.SendKeys ("<Enter>")
                                            Do While Sess0.Screen.OIA.XStatus <> 0
                                                DoEvents
                                            Loop
                                                
                                            'Screen Scrape coding will be placed here
                                            ML = Trim(Sess0.Screen.GetString(2, 23, 27))
                                            If ML = "MEMBER LIFE PROFILE INQUIRY" Then '8
                                                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))
                                                CR = 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, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                            Else '8
                                                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
                                                Sheets("Workbook").Cells(rw1, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                                End If '8
                                            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
                                                    Sess0.Screen.MoveTo 5, 22
                                                    Sess0.Screen.SendKeys ("CD")
                                                    Sess0.Screen.SendKeys ("<Enter>")
                                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                                        DoEvents
                                                    Loop
                                                
                                                Else
                                                End If
                                            End If '7
                                    
                                    ElseIf PG = "03" Then '6
                                    
                                        rw2 = rw2 + 1
                                    
                                        Sess0.Screen.SendKeys ("<Pf7>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                
                                            S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                            I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                            
                                            If S = "" And I = "" Then '7
                                            Exit Do
                                        
                                            Else '7
                                            Sess0.Screen.MoveTo 5, 18
                                            Sess0.Screen.SendKeys I
                                            Sess0.Screen.SendKeys ("<Enter>")
                                            Do While Sess0.Screen.OIA.XStatus <> 0
                                                DoEvents
                                            Loop
                                                
                                            'Screen Scrape coding will be placed here
                                            ML = Trim(Sess0.Screen.GetString(2, 23, 27))
                                            If ML = "MEMBER LIFE PROFILE INQUIRY" Then '8
                                                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))
                                                CR = 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, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                            Else '8
                                                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
                                                Sheets("Workbook").Cells(rw1, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                                End If '8
                                            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
                                                    Sess0.Screen.MoveTo 5, 22
                                                    Sess0.Screen.SendKeys ("CD")
                                                    Sess0.Screen.SendKeys ("<Enter>")
                                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                                        DoEvents
                                                    Loop
                                                
                                                Else
                                                End If
                                            End If '7
                                    
                                    ElseIf PG = "04" Then '6
                                        
                                        rw2 = rw2 + 1
                                    
                                        Sess0.Screen.SendKeys ("<Pf7>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                
                                            S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                            I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                            
                                            If S = "" And I = "" Then '7
                                            Exit Do
                                        
                                            Else '7
                                            Sess0.Screen.MoveTo 5, 18
                                            Sess0.Screen.SendKeys I
                                            Sess0.Screen.SendKeys ("<Enter>")
                                            Do While Sess0.Screen.OIA.XStatus <> 0
                                                DoEvents
                                            Loop
                                                
                                            'Screen Scrape coding will be placed here
                                            ML = Trim(Sess0.Screen.GetString(2, 23, 27))
                                            If ML = "MEMBER LIFE PROFILE INQUIRY" Then '8
                                                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))
                                                CR = 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, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                            Else '8
                                                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
                                                Sheets("Workbook").Cells(rw1, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                                End If '8
                                            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
                                                    Sess0.Screen.MoveTo 5, 22
                                                    Sess0.Screen.SendKeys ("CD")
                                                    Sess0.Screen.SendKeys ("<Enter>")
                                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                                        DoEvents
                                                    Loop
                                                
                                                Else
                                                End If
                                            End If '7
                                    
                                    ElseIf PG = "05" Then '6
                                    
                                        rw2 = rw2 + 1
                                    
                                        
                                        Sess0.Screen.SendKeys ("<Pf7>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf8>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                            S = Trim(Sess0.Screen.GetString(r, 33, 8))
                                            I = Trim(Sess0.Screen.GetString(r, 2, 2))
                                            
                                            If S = "" And I = "" Then '7
                                            Exit Do
                                        
                                            Else '7
                                            Sess0.Screen.MoveTo 5, 18
                                            Sess0.Screen.SendKeys I
                                            Sess0.Screen.SendKeys ("<Enter>")
                                            Do While Sess0.Screen.OIA.XStatus <> 0
                                                DoEvents
                                            Loop
                                                
                                            'Screen Scrape coding will be placed here
                                            ML = Trim(Sess0.Screen.GetString(2, 23, 27))
                                            If ML = "MEMBER LIFE PROFILE INQUIRY" Then '8
                                                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))
                                                CR = 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, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                            Else '8
                                                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
                                                Sheets("Workbook").Cells(rw1, 2) = CONT
                                                Sheets("Workbook").Cells(rw1, 4) = ENTITY
                                                Sheets("Workbook").Cells(rw1, 5) = PRDGRP
                                                Sheets("Workbook").Cells(rw1, 11) = MA
                                                Sheets("Workbook").Cells(rw1, 12) = LA
                                                Sheets("Workbook").Cells(rw1, 13) = SN
                                                Sheets("Workbook").Cells(rw1, 14) = SR
                                                Sheets("Workbook").Cells(rw1, 15) = CT
                                                Sheets("Workbook").Cells(rw1, 16) = ST
                                                Sheets("Workbook").Cells(rw1, 17) = ZP
                                                
                                                End If '8
                                            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
                                                    Sess0.Screen.MoveTo 5, 22
                                                    Sess0.Screen.SendKeys ("CD")
                                                    Sess0.Screen.SendKeys ("<Enter>")
                                                    Do While Sess0.Screen.OIA.XStatus <> 0
                                                        DoEvents
                                                    Loop
                                                
                                                Else
                                                End If
                                            End If '7
                                    Else: End If '6
        
                                Next r '5
                                                            
                                Sess0.Screen.SendKeys ("<Pf8>")
                                Do While Sess0.Screen.OIA.XStatus <> 0
                                    DoEvents
                                Loop
                                
                                PR = Sess0.Screen.GetString(8, 5, 4)
                                Do While Sess0.Screen.OIA.XStatus <> 0
                                    DoEvents
                                Loop
                                
                                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
                                        Sess0.Screen.MoveTo 5, 22
                                        Sess0.Screen.SendKeys ("CD")
                                        Sess0.Screen.SendKeys ("<Enter>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                        
                                        Sess0.Screen.SendKeys ("<Pf7>")
                                        Do While Sess0.Screen.OIA.XStatus <> 0
                                            DoEvents
                                        Loop
                                    
                                    Else: End If
                                
                        Loop '4
                            
                    Else '3
                     
                        Sess0.Screen.SendKeys ("<Pf2>")
                        Do While Sess0.Screen.OIA.XStatus <> 0
                            DoEvents
                        Loop
                        
                    End If '3
                    
                                    
             [b]Next X  'next row/group '2[/b]
            
            Loop '1
        
        End With

End Sub
 
Upvote 0 Downvote
So, bottom line, what's the result?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
It’s running perfectly for one input. I just recently tested it with multiple inputs (i.e. the PO values) and realized I’m missing some syntax to execute after the initial value so I’ll need to review it a bit more in break mode to see exactly where the disconnect(s) occur but should be relatively simple. Once I get that completed I’ll post it in case you can give me some pointers on cleaning it up :)
 
Upvote 0 Downvote
Great! I'll be happy to review your progress.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

247xavi
  • Locked
  • Question
Attachmate to BlueZone
Replies
0
Views
125
247xavi
247xavi
nightrider69
  • Locked
  • Question
Micro mainframe transmission issues using Attachmate myExtra
Replies
0
Views
153
nightrider69
nightrider69
FreCard
  • Locked
  • Question
Extra macro example
Replies
3
Views
265
SkipVought
S
CADD6953
  • Locked
  • Question
Macro for Excel to Attachmate and Back Again 1
2
Replies
34
Views
492
SkipVought
S
AlbertShenko
  • Locked
  • Question
Interacting with Attachmate dialog box at login 1
Replies
8
Views
251
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top