Read from file and Loop Problem
Read from file and Loop Problem
(OP)
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
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
RE: Read from file and Loop Problem
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!
RE: Read from file and Loop Problem
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
RE: Read from file and Loop Problem
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
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!
RE: Read from file and Loop Problem
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
RE: Read from file and Loop Problem
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!
RE: Read from file and Loop Problem
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..
RE: Read from file and Loop Problem
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
RE: Read from file and Loop Problem
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!
RE: Read from file and Loop Problem
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
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
RE: Read from file and Loop Problem
Thanks,
RE: Read from file and Loop Problem
SIMPLE!
Skip,
Just traded in my old subtlety...
for a NUANCE!