How to quickly count number of lines in a CSV or Text File?

[zaphod2003]

I have a macro which compiles CSV data received from an AS400.

Because the number of records can be huge (400,000 + lines). I used a progress bar to display how long the process is likely to take.

To find out the number of records I use this routine:

 
Open sFileName For Input As #1
Do While Not EOF(1)
    Line Input #1, sLineOfText              
    recordCounter = recordCounter + 1
Loop
Close

Which is fine for small files, but causes a considerable delay in processing when the number of records reach huge amounts.

Is there a way I could find out the approximate or exact number of records inside the CSV without having to literally count the number of lines first?