Read from file and Loop Problem 1

vicjalan · May 3, 2010

Hi all, I wrote a macro that will take a SSN input it and scrape the screen. This was all fine and dandy until now that I have to do the same for 20 or 30 SSNs at a time. I tried adding to the code to read from the file and loop until eof but I get a file already open error.

Seems logical to me but I'm not much of a programmer

Here is what I have:

' Get the next available file number
filenum2% = FreeFile

' Open the file.
Filename2$="C:\ssn.txt"
Open FileName2$ For Input as filenum2%

' Initialize Screen Buffer
screenbuf$ = ""

' Determine the size of the Presentation Space
MaxRows%=MyScrn.Rows()
MaxColumns%=MyScrn.Cols()

' Initialize variables to hold screen information
linebuf$ = Space$ (MaxColumns%)

do

Input # filenum2%, ssnum$
MyScrn.Sendkeys(ssnum$ + "<Enter>")
MyScrn.WaitHostQuiet(g_HostSettleTime)

' Copy the Presentation space
For row% = 3 to 4

' Get a row of data from the host screen
linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
screenbuf$ = screenbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)
Next

' Copy the Presentation space
For row% = 8 to 9

' Get a row of data from the host screen
linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
screenbuf$ = screenbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)

Next

' Copy the Presentation space
For row% = 10 to 22

' Get a row of data from the host screen
linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
screenbuf$ = screenbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)

Next

' Move to the next Page if applicable
If MyScrn.GetString(24,2,2) <> "LAST PAGE" Then
MyScrn.Sendkeys("<pF8>")
End If

MyScrn.WaitHostQuiet(g_HostSettleTime)

' Copy the Presentation space
For row% = 12 to 22

' Get a row of data from the host screen
linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
screenbuf$ = screenbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)

Next

' Get the next available file number
filenum% = FreeFile

' Open the file.
FileName$="C:\scrnout.txt"
Open FileName$ For Output as filenum%

' Print the screen with a form feed
Print # filenum%, screenbuf$;

'Close file
Loop until eof(filenum2%)
Close filenum%
System.TimeoutValue = OldSystemTimeout

SkipVought · May 3, 2010

Hi,

You are opening your OUTPUT file within your Do Loop each time your loop cycles. Why not open it when you open your INPUT file?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

vicjalan · May 3, 2010

Thanks! That was easy

.. I kept breaking my head moving pieces of code around and I never thought about that.

I got a new issue now..In my ssn.txt file I have 3 entries:

000000000
111111111
222222222

The screens for 000000000 are copied to the scrnout.txt 3 times, 111111111 gets copied to the scrnout.txt 2 times, and 222222222 gets copied once..

Each number has multiple pages(or screens), hence this part:

' Move to the next Page if applicable
If MyScrn.GetString(24,2,2) <> "LAST PAGE" Then MyScrn.Sendkeys("<pF8>")
End If

I don't get why it's doing that..is it a timing issue?

Thanks again for your help

SkipVought · May 3, 2010

The screens for 000000000 are copied to the scrnout.txt 3 times, 111111111 gets copied to the scrnout.txt 2 times, and 222222222 gets copied once..

And why would that be? Please explain in detail.

Within your Do...Loop where you read a new SSN, I assume, yo umust have another Do...Loop to access each screen for the ssn...

Code:

    do 
    
    Input # filenum2%, ssnum$
       do
          MyScrn.Sendkeys(ssnum$ + "<Enter>")
           MyScrn.WaitHostQuiet(g_HostSettleTime)

       loop until MyScrn.GetString(24,2,2) <> "LAST PAGE" 
    Loop until eof(filenum2%)

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

vicjalan · May 3, 2010

And why would that be?

That's what I'd like to know

When I look at the output I see this:

000000000
<data>

000000000
<data>

111111111
<data>

111111111
<data>

000000000
<data>

222222222
<data>

What I should get is:

000000000
<data>

111111111
<data>

222222222
<data>

I do not have another do loop to access each screen for each ssn. What I have is the IF statement, IF the screen does not show LAST PAGE then the code should assume there are multiple screens and it needs to pF8 to move to the next screen and scrub it's data.

Are you saying that I should have a 2nd do loop? If so where would I put it, how would it look?

Thank you

SkipVought · May 3, 2010

Are you saying that I should have a 2nd do loop? If so where would I put it, how would it look?

I DID!

However, if you do NOT want to loop thru all the screens for the ssn, then do not issue a pf8.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

vicjalan · May 3, 2010

I understand what's happening now..

My issue is that there is no real way for the code to know when it reaches the last page..

For example

I'm telling the code to assume there are multiple pages if it does not find "PAGE 1 OF 1" and keep pressing pf8..

The code doesn't know when it reaches the end because no error is thrown if pf8 is pressed and there are no more screens, it just refreshes the screen.

There is also a "LAST PAGE" on the very last screen, however if a SSN only has 1 screen (PAGE 1 OF 1), it doesn't show the text "LAST PAGE" until pf8 is pressed.

Both of these scenarios will cause my screen to get scrubbed multiple times.

Thanks for your help..

vzachin · May 4, 2010

hi vic,

if the screen shows "PAGE 1 OF 1" or "PAGE 10 of 20", then it is possible to code that...

you would PF8 when Page ## does not equal of ##...

post your entire code if you can

zach

SkipVought · May 4, 2010

Why even DO the PF8 thing, if you do not want the data on each page?
For each ssn
Put your ssn
Get your data
write your data wherever
next ssn

SIMPLE!!!

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

vicjalan · May 5, 2010

Why even DO the PF8 thing, if you do not want the data on each page?

If at some point I said that I didn't want the data, I apologize..I do want the data on all screens. The problem I was having is that some of the data on some screens were duplicating..

What I ended up doing is doing IF ElseIF statements for each individual page. I don't really like because it's alot of repetitive code and I must explicitily code for each page..

See the code below:

Code:

' Get the next available file number
        filenum% = FreeFile    

' Open the file.
        FileName$="C:\Scrn_Output\" + Format(Date, "yymmdd") + "_" + Format(Time, "hhmmss") + ".txt"
        Open FileName$ For Output as filenum%

' Get the next available file number
        filenum2% = FreeFile    

' Open the file.
        Filename2$="C:\ssn.txt"
        Open FileName2$ For Input as filenum2%

' Initialize Screen Buffer
        screenbuf$ = ""       
    
' Determine the size of the Presentation Space
        MaxRows%=MyScrn.Rows()
        MaxColumns%=MyScrn.Cols()
 
' Initialize variables to hold screen information
        linebuf$ = Space$ (MaxColumns%)
    
    do
        Input # filenum2%, ssnum$
        MyScrn.Sendkeys(ssnum$ + "<Enter>")
        MyScrn.WaitHostQuiet(g_HostSettleTime)
        

 
' Get a row of data from the host screen
        acct$ = MyScrn.GetString(4,3,9)
        
' Store the line read into screenbuf$
        acctbuf$ = screenbuf$ + acct$ + chr$ (32)
      
    If MyScrn.GetString(7,68,13) = "PAGE  1 OF  1" Then
        For row% = 12 to 22
' Get a row of data from the host screen
        linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
        screenbuf$ = screenbuf$ + acctbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)
    Next
    
' Print the screen with a form feed
        Print # filenum%, screenbuf$;
        screenbuf$ = ""
        MyScrn.WaitHostQuiet(g_HostSettleTime)
    
    ElseIf MyScrn.GetString(7,68,13) = "PAGE  1 OF  2" Then
      
        For row% = 12 to 22
' Get a row of data from the host screen
        linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
        screenbuf$ = screenbuf$ + acctbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)
    Next
    
' Move to the next Page if applicable
        MyScrn.Sendkeys("<pF8>")
        MyScrn.WaitHostQuiet(g_HostSettleTime)
    
        For row% = 12 to 22
' Get a row of data from the host screen
        linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
        screenbuf$ = screenbuf$ + acctbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)
    Next
    
' Print the screen with a form feed
        Print # filenum%, screenbuf$;
        screenbuf$ = ""
        MyScrn.WaitHostQuiet(g_HostSettleTime)
        
    ElseIf MyScrn.GetString(7,68,13) = "PAGE  1 OF  3" Then
      
        For row% = 12 to 22
' Get a row of data from the host screen
        linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
        screenbuf$ = screenbuf$ + acctbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)
    Next
    
' Move to the next Page if applicable
        MyScrn.Sendkeys("<pF8>")
        MyScrn.WaitHostQuiet(g_HostSettleTime)
    
        For row% = 12 to 22
' Get a row of data from the host screen
        linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
        screenbuf$ = screenbuf$ + acctbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)
    Next
    
' Move to the next Page if applicable
        MyScrn.Sendkeys("<pF8>")
        MyScrn.WaitHostQuiet(g_HostSettleTime)
    
        For row% = 12 to 22
' Get a row of data from the host screen
        linebuf$ = MyScrn.Area(row%, 2, row%, MaxColumns%, , xBlock)

' Store the line read into screenbuf$
        screenbuf$ = screenbuf$ + acctbuf$ + linebuf$ + Chr$ (13) + Chr$ (10)
    Next
    
' Print the screen with a form feed
        Print # filenum%, screenbuf$;
        screenbuf$ = ""
        MyScrn.WaitHostQuiet(g_HostSettleTime)

    End If
    
'Close file
        Loop until eof(filenum2%)
    
    Close filenum%
        MyScrn.WaitHostQuiet(g_HostSettleTime)
        System.TimeoutValue = OldSystemTimeout

Thanks

vicjalan · May 5, 2010

vzachin I will try what you suggested and I'll post the results.

Thanks,

SkipVought · May 5, 2010

some of the data on some screens were duplicating..

Then at the end of the appropriate loop you must STORE a PREVIOUS VALUE, then when you get the next value, you, first off, compare the current value with the previous value, and decide what to do.

SIMPLE!

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

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!

Read from file and Loop Problem 1

vicjalan

IS-IT--Management

SkipVought

Programmer

vicjalan

IS-IT--Management

SkipVought

Programmer

vicjalan

IS-IT--Management

SkipVought

Programmer

vicjalan

IS-IT--Management

vzachin

Technical User

SkipVought

Programmer

vicjalan

IS-IT--Management

vicjalan

IS-IT--Management

SkipVought

Programmer

Similar threads

Part and Inventory Search

Sponsor