# lines using Open, EOF etc.
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: # lines using Open, EOF etc.

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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


  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you looked at the automating the Text import wizard?







  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.






  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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





  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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/viewtop...c=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



  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com