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 IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Read from file and Loop Problem 1

Status
Not open for further replies.
vicjalan

vicjalan

IS-IT--Management
Jan 18, 2010
6
US
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
 
Sort by date Sort by votes


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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Thanks! That was easy :D.. 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
 
Upvote 0 Downvote


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..
Click to expand...
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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
And why would that be?
Click to expand...

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
 
Upvote 0 Downvote



Are you saying that I should have a 2nd do loop? If so where would I put it, how would it look?
Click to expand...
I DID!

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

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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..
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote



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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Why even DO the PF8 thing, if you do not want the data on each page?
Click to expand...

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
 
Upvote 0 Downvote
vzachin I will try what you suggested and I'll post the results.

Thanks,
 
Upvote 0 Downvote
some of the data on some screens were duplicating..
Click to expand...
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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

jeaneshe
  • Locked
  • Question
How do I select different Excel cells using a variable column loop?
Replies
10
Views
299
SkipVought
S
2009luca
  • Locked
  • Question
LOOP on cursor position....
Replies
1
Views
108
SkipVought
S
2009luca
  • Locked
  • Question
send to txt file
Replies
0
Views
115
2009luca
2009luca
planetech
  • Locked
  • Question
enter number and print to file? 1
Replies
14
Views
224
planetech
planetech
mdaiber614
  • Locked
  • Question
Macro to Copy Text from IE Browser and Paste in MS Word
Replies
4
Views
293
mdaiber614
mdaiber614

Part and Inventory Search

Sponsor

Back
Top