Can VB count the number of lines in a text file . . . .

  • Thread starter Thread starter Steve
  • Start date Start date
Sub ReadStraightTextFile()
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
Do While Not EOF(1)
Line Input #1, LineofText
rw = rw + 1
Loop
'Close the file
Close #1
Worksheets("Sheet3").Range("B9").Value = rw
End Sub

If the lines are fixed width, then you might be able to just do some math on
the file size.
 
Hi Steve,

You could use the FileSystemObject in a user-defined function (UDF) like
this:

Public Function GetLineCount(rsPath As String) As Variant
Dim fso As Object

On Error GoTo ErrHandler

Set fso = CreateObject("scripting.filesystemobject")

With fso.OpenTextFile(rsPath)
If Not .AtEndOfStream Then
.ReadAll
GetLineCount = .Line
Else
GetLineCount = 0
End If
.Close
End With

ExitRoutine:
Set fso = Nothing
Exit Function
ErrHandler:
GetLineCount = CVErr(1004)
Resume ExitRoutine
End Function


Then, from an cell, you could do this:

=GETLINECOUNT("C:\test.txt")

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Steve,

Try the sub below.

HTH,
Bernie
MS Excel MVP

Sub ShowFileLineSize()
Dim FileName As String
Dim ResultStr As String
Dim FileNum As Integer
Dim Counter As Double

FileName = Application.GetOpenFilename
If FileName = "" Then End

FileNum = FreeFile()

Open FileName For Input As #FileNum

Counter = 0

Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
Counter = Counter + 1
Loop

Close
Range("A1").Value = FileName & " is " & Counter & " lines long."
End Sub
 
Hi Jake. Just to share programming ideas. I have found that reading in a
very large file (via ReadAll, or just looping) can slow the program down a
little. Here is a programming trick that I have used for awhile. HTH.

Function NumberOfRecords(sFile As String) As Double
' = = = = = = = = = = = = = = = = = = = = = = = =
'// By: Dana DeLouis
'// Returns the number of lines in a text file
'// Use: Answer = NumberOfRecords("C:\Test.txt")
' = = = = = = = = = = = = = = = = = = = = = = = =
Dim f As Object
With CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set f = .OpenTextFile(sFile, 8) ' 8 -> Appending
If Err.Number > 0 Then
NumberOfRecords = 0
Else
NumberOfRecords = f.Line - 1
f.Close
Set f = Nothing
End If
End With
End Function


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Jake Marx said:
Hi Steve,

You could use the FileSystemObject in a user-defined function (UDF) like
this:

Public Function GetLineCount(rsPath As String) As Variant
Dim fso As Object

On Error GoTo ErrHandler

Set fso = CreateObject("scripting.filesystemobject")

With fso.OpenTextFile(rsPath)
If Not .AtEndOfStream Then
.ReadAll
GetLineCount = .Line
Else
GetLineCount = 0
End If
.Close
End With

ExitRoutine:
Set fso = Nothing
Exit Function
ErrHandler:
GetLineCount = CVErr(1004)
Resume ExitRoutine
End Function


Then, from an cell, you could do this:

=GETLINECOUNT("C:\test.txt")

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

and put that number into a excel cell?????
I would love to know how to do that. . . .
Click to expand...
Click to expand...
 
Hi Dana,

Dana said:
Hi Jake. Just to share programming ideas. I have found that reading
in a very large file (via ReadAll, or just looping) can slow the
program down a little.
Click to expand...

Yes, I was aware of the slowdown with big text files, but I hadn't thought
of opening it in Append mode to get the line # immediately. Great idea -
thanks!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Back
Top