Capture data on screens till exhausted,then export that data to Excel 1

[MrHeynow]

Sorry, over the past several days I’ve viewed over a hundred threads and I still cant piece together a good solution. I have these screens listing several transactions. There could be one page to over 100 pages. What I want to do is to copy/copy append or capture data in a select area from each page then export all the gathered data to excel. However I don’t know if there will be two pages or 102 pages, I have to <pf8> until the last page and then words of “NO MORE DATA” are displayed.

I believe these to be the key objects;

I'm using Attachmate's Extra Personal Client
Page Value, the last digit is located @ 01/68 and expands to the left. IE the page 102 would start @ 01/66 and end @ 01/68.
Indicator of more pages is the word “(CONTINUED)” and its located @ 20/03.
The desired data or area with the headers is located 06/03 to 18/80.
The desired data or area without the headers would be located 08/03 to 18/80.
The indicator of no more records, are the words “NO MORE DATA” and they are located @ 07/03.
Use PF7 to go up or back a page.
Use PF8 to go down or forward a page.
<Home> is located @ 02/16.

Below is a mock example of my screen (Pic1), I have selected page two to display the where the word “(CONTINUED)” appears. Along with displaying the functions of the PF7 and PF8.

Then in Pic2 I’m displaying just the bottom of the final page, it happens to be page 64 right now and where the words of “NO MORE DATA” appear. Thus indicating that there are no more pages. Along with displaying the functions of the PF7 and PF8.

I know this feasible, likely child’s play for the experts that I have read in oh so many posts. I just can’t afford to spend any more time in my learning method of cutting , pasting adapting and testing to get the desired data. Your assistance your teaching will be greatly appreciated.

Lastly, I truly appreciate this website, the members and the forum, it’s a great resource.

Pic1…..
XXXX XXX1 DISPLAY PAGE 2 04/28/11
NEXT FUNC==> 11:21:11
OPEN ITEMS FOR TRANSACTION DATE 04/28/11
AND LOCALE 849

--TRAN-- PART XYZ MEMO MEMO
TIME TC FT R RECORD AMOUNT RECEIPTS EARNINGS RECEIPTS CREDITS
080817 YT 01 1 123456789 250.00 250.00 50.00
080831 YT 01 1 234567890 500.00 500.00
080843 YT 01 1 345678901 200.00 200.00 40.00
080914 YT 01 1 456789012 100.00 100.00 33.30
080941 YT 01 1 567890123 1537.76 1537.76 461.33
080956 YT 01 1 678901234 411.00 411.00
081024 YT 01 1 789012345 200.00 200.00 100.00
081034 YT 01 1 890123456 249.00 249.00
081034 YU 01 1 901234567
081044 YT 01 1 012345678 210.64 210.64 29.49
081056 YT 01 1 112345678 50.00 50.00

(CONTINUED)
RMO ==> N
OPTION ==> ACCOUNT ==> DATE ==> OFFICE ==>

PF7=UP PF8=DOWN
(End of Pic1)

Pic2…..
RMO ==> N
OPTION ==> ACCOUNT ==> DATE ==> OFFICE ==>
NO MORE DATA
PF7=UP PF8=DOWN

 

[vzachin]

how about posting what code you have? you are not saying what problems you are experiencing. in order for somebody to help you, you need to be precise in what you are asking for.

saying " I just can't afford to spend any more time" truly turns people off...


in a nutshell:

do
if sess.getstring (20,3,9) = "CONTINUED" then
sess.sendkeys ("<pf8>") 'page foward
'code goes here to place data in excel
else
exit do
loop

zach

 

[MrHeynow]

Zach, Everyone

It's not my intention to drive away any assistance but to garner it. I’m not having a problem with my code; I have no real code and why there are the specifics in my post.

Your nutshell is conceptually correct and it displays part of the problem I’m having in reviewing the solutions in the replies slash posts. It seems most replies assume a higher level of knowledge than I and likely others pocess. In the past several days I suspect I have seen every part of the code that can do what I am trying to do, however, the responses are not inclusive of the other or initial code required to even arrive at the solution or compile far enough to understand what really is at issue.

Your nutshell code assumes I know what is above and below it to make it work. While you and others will look at your sample code and think to yourselves “Well this is a Loop without a Do“

I believe this post and the solution if provided, has the potential of being one of the very best. As the solution will demonstrate 6 keys methods or codes of; getting and evaluating strings, if statements , loop statements, screen navigation and use or exporting to other software.

I remain appreciative of this forum, its experts and hopefull of assistance.

 

[SkipVought]

This is often an iterative question and answer process. Many times a question, repsonding question, response and answer process.

So we await your TRYING the code that you have assembled and reporting what problem(s) you are having; error messages, screen behavior, variable contents where applicable.

You will NOT receive a turn-key solution here. You will have to hire a programmer to do that. But you can get lots of good "tips" & code segments if you persevere.

If you're in a hurry, this is probably not the place to garner the information you need.

PROCESS -- its a PROCESS.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrHeynow]

Okay fellas I hope you both well

I have this code running and it does eveything but secure the last page. Meaning runs through all the pages and it puts all the records into the text file accept the last page likely because it doesnt say "Continue" in fact there really is nothing that denotes it the last page, it just is.

Here is the code that works to the describe point.

Declare Sub Wait()

Const S_PATH = "C:\Temp\"
Dim Sys as Object
Dim Sess as Object

Sub Main()
Dim inFile$, outFile$, aLine$
Dim sDir$, sInc$, sJunk$, sPath$


sInc = ""
inFile = S_PATH & "Capture012Inc.txt"
sDir = Dir(inFile, 0)
If sDir <> "" Then
Open inFile For Input As #1
Do While EOF(1) = 0
Line Input #1, sJunk
If IsNumeric(sJunk) Then
sInc = sJunk
End If
Loop
Close #1
End If

Set Sys = CreateObject("Extra.System")
If Sys Is Nothing Then
MsgBox ("Could not create Extra.System...is E!PC installed on this machine?")
Exit Sub
End If

Set Sess = Sys.ActiveSession
If Sess Is Nothing Then
MsgBox ("No session available...stopping macro playback.")
Exit Sub
End If

If UCase(Sess.Screen.GetString(20, 4, 9)) = "CONTINUED" Then
iRows = Sess.Screen.Rows
iCols = Sess.Screen.Cols

outFile = S_PATH & "Capture012"&".txt"
Open outFile For Output As #1
Do
For i = 1 To 24
aLine = UCase(Trim(Sess.Screen.GetString(i, 1, 80)))
Print #1, aLine
Next
Sess.Screen.Sendkeys("<Pf8>")
Wait
Loop While Trim(Sess.Screen.GetString(20, 4, 9)) = "CONTINUED"
Close #1

Open inFile For OutPut As #1
Print #1, CStr(CInt(sInc))
Close #1
Else
MsgBox "You must be on page one of funct 33 using option 4 to run macro."
End If
End Sub

Sub Wait()
Do While Sess.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End Sub

 

[vzachin]

MrHeyNow,

did i understand your post correctly, that you have a problem recognizing the last page?


zach

 

[MrHeynow]

Yes Zach, there may be details on it but since it doesnt have "continued" on the page, no data is scraped.

 

[vzachin]

something like this should work:

    Do
        For i = 1 To 24
            aLine = UCase(Trim(Sess.Screen.GetString(i, 1, 80)))
            Print #1, aLine
        Next
        
        If Trim(Sess.Screen.GetString(20, 4, 9)) = "CONTINUED" Then
            Sess.Screen.SendKeys ("<Pf8>")
            Wait
        Else
        
            Exit Do
            
        End If

    Loop

 

[MrHeynow]

Ta Da!

Zach that did the trick thank you and thanks Skip where ever you are.

 

[3gm]

I have a similar, but much fancier, version of this approach using Attachmate Reflection VBA and the Excel object model. Good shop standards helped out there as we always display "Page xxx of xxx" in the upper right corner of multi-page screens. Makes handling the paging easier. The macro is executed via a toolbar button and produces a nicely formatted spreadsheet ready for printing while returning the user to his or her starting page (after navigating through all of the detail records, going over to some other screens, etc).

Development of this sort of thing is generally best accomplished in small, stepwise increments, e.g. figure out how to page through the screens and gather the necessry data then figure out how to create an empty spreadsheet, format it, load in constant data, then variable data from the screens.

Glad to see you were able to persevere and get yours working!

Glenn