# lines using Open, EOF etc.

[John Yazou]

Hi,
I have a file that contains
Line1
Line2
Line3

Am using something like

while not eof(filenumber)
if Condition1 = True then
copied = copied + 1
do something....
end if
total = total + 1
loop

Which is fine... but now i want to add a progress bar.. but I will need to set the ProgressBar1.Max = total, so I will need to know the # of lines contained in the file beforehand. How to do this??

want it to be something like

ProgressBar1.Max = # of lines
ProgressBar1.Min = 0

while not eof(filenumber)
if Condition1 = True then
copied = copied + 1
do something...
end if
ProgressBar1.Value = total + 1

loop

 

[Dave Hawley]

John

Not to sure what you are doing but

Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox LastRow
End If

 

[John Yazou]

That would work if the data is in a sheet correct?? But the data is in a text file.
The code is a simplified text file parsing program. I want the users the see the progress.

 

[Dave Hawley]

Have you looked at the automating the Text import wizard?

 

[John Yazou]

Yup,
Here's the problem:
- The raw data file is about 20+ MB, text file, containing about 1 million rows; so using Text Import wizard, or using Application.OpenText is out
- However, the actual meaningful data is only a fraction of the file, and would actually fit in Excel

So, the full import function looks something like..
FilterRawData("temp.txt") ' As previous posting
Application.OpenText xxx yyy zzz

Its been working fine.. but my boss, wants to see the progress.. I don't know why.. If there's no solution I guess would have to tell him to live with it... Thanks

 

[Dave Hawley]

Hi John

Tell your boss if he has nothing better to do than watch a progress bar he should retire Joking of course!

I am a little confused, if there are one million or so lines and you only want x number of these, your progress bar max Property will have to be set to ONLY the lines you need. I assume you your loop actually does this for you?

I guess what I am saying is, even it there is a way to get the number of lines from the Text File (don't think there is) it would be wrong.

 

[John Yazou]

Thanks Dave...
Well, good point.. my boss does have nothing better to do... ... he's one of those ppl who thinks just because the UI is a bit unresponsive .. it hangs...

Guess i'll just tell him it cannot be done... no harm done... cheers dave

 

[Legacy 98055]

Hi
There are several ways you could do it.

Use file length function.

If the data lines are made up of variable length strings, just give a good guess for an average number of bytes per line. A variable length string is numCharacters + 2
Spaces count as characters.

For an open file use LOF(File#)/EstimatedBytesPerLine


For a closed file use FileLen(Path/Name)/EstimatedBytesPerLine

If the data is stored in the file on each line as a Data type such as date, byte, integer, ect...
Then search help for the byte count for whichever data type applies...

Here is an example given the most difficult situation: Variable length strings per line on your file.


Dim EstimatedBytesPerLine
Dim LengthOfThisFile
Dim ProgressBarMax_Value
Dim CurrentFileNum

CurrentFileNum = FreeFile

Open your file as #CurrentFileNum

ProgressBarMax_Value = LOF(CurrentFileNum)/EstimatedBytesPerLine

Anyway, I hope this helps you...
Tom

 

[Dave Hawley]

Tom, the problem is that there are a million or so lines, but only x amount are needed, not all of them.

John, you may find the method I often use to inform a user of a very long macro running.
http://www.mrexcel.com/board/viewtopic.php?topic=3412&forum=2

The advantage of this method is it will not slow down an already slow prodedure, something the Progress bar does. It Catch 22

 

[Legacy 98055]

I agree Dave.
I don't use them myself.
Not with Excel.
I just throw the code into a userform and place the wait message on the caption. Will check out your idea too!
Am learning too much here.
Head might explode soon.
Have a nice day!
Tom