×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

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

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

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

(OP)
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.

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



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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

(OP)
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

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

MrHeyNow,

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


zach

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

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

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

something like this should work:

CODE

    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

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

(OP)
Ta Da!

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close