MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Count Lines in a Text File

Posted by Scott Staples on August 04, 2000 8:48 AM

Is there a way to count the lines in a text file before it is imported? I need to import 3 different text files with no set number of lines and I don't want to have overlap or gaps. Any help is appreciated.

Posted by Ivan Moala on August 05, 0100 6:46 AM

Hereis a routine to count the number of lines
for a TEXT file only as diff file formates will
give you diff results depending on the formating.

Sub ReadNoLines_text()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim CountLines As Double

'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Set The CountLines to 1
CountLines = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
'Increment the CountLines By 1
CountLines = CountLines + 1
'Start Again At Top Of 'Do While' Statement

'Close The Open Text File
MsgBox "Number of lines for " & FileName & " = " & CountLines - 1
End Sub