Record count in a text file

dsimcox

Board Regular
Joined
Dec 8, 2004
Messages
75
I want to determine the number of records in a text file beforeimporting the file into an Excel worksheet.

Is there a way to query the file for this information using VBA?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mdusoe

Active Member
Joined
Aug 27, 2004
Messages
428
Depends. If you are talking about a file where one records = one line, then you could use something like this:

Code:
' Open the file.  Read the whole thing into one big string.
    Open "myfile.txt" For Binary Access Read As #1
    strFile = String(LOF(1), " ")
    Get #1, , strFile
    Close #1
    
' create an array from the records in the string
    aryRecords = Split(strFile, vbCrLf)

' The number of records is the UBound of the array + 1.
    RecCount = UBound(aryRecords) + 1

Does this work?
Mike.
 

dsimcox

Board Regular
Joined
Dec 8, 2004
Messages
75
Perfect!

This is exactly what I had hoped to accomplish.

I didn't realize you could read in a string of 280000 records so quickly.

Thank you Mike!

:wink:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,819
Messages
5,574,504
Members
412,599
Latest member
Schu94
Top