Count Number of Lines in a .txt file

Hello,

Right now I have a macro that points to a .txt file and begins importing the data into an .xls file. As it imports the .txt file, it counts the records.

The point of this part of the macro is to determine if the .txt file will exceed the row limit of about 65,000 of excel. So whenever the count > 65,000, the macro adds a new worksheet. This part of the macro is a loop and has to import and count every row. Obviously, this takes a good long time.

I noticed in the .txt file, in the status bar, is data that has already counted the rows/lines.

My want:

Is there any way I can have the macro -

1. Point to the .txt file (no problem here)
2. Then, extract or pull that row/line count number
3. Test that number if it over x amount of lines
4. And then end with IF over X amount, then...
5. Or, IF under X amount, then...

Any tips would be great! Thanks!!!

Thank you for the suggestions. I will try it out and let you know!

Hello macro_noob,

If you are interested, here is another macro to count the lines of text file. This method uses the File System Object (FSO).

Function GetLineCount(ByVal File_Path As String) As Long

  Dim FSO As Object
  Dim N As Long
  Dim Txt As String
  Dim TxtFile As Object
  
    On Error GoTo Finished
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
      Set TxtFile = FSO.GetFile(File_Path).OpenAsTextStream(1, False)
      
      While Not TxtFile.AtEndOfStream
        TxtFile.ReadLine
        N = N + 1
      Wend
      
Finished:
    If Err <> 0 Then MsgBox "File Error!"
    TxtFile.Close
    GetLineCount = N
    Set FSO = Nothing
    
End Function

Sincerely,
Leith Ross

Originally Posted by macro_noob

Hello,

My want:

Is there any way I can have the macro -

1. Point to the .txt file (no problem here)
2. Then, extract or pull that row/line count number
3. Test that number if it over x amount of lines
4. And then end with IF over X amount, then...
5. Or, IF under X amount, then...

Any tips would be great! Thanks!!!

try

Sub test()
Dim fn As String, temp As String, x, i As Long, ii As Long, y
Dim delim As String, t As Long, a(), n As Long, maxCol As Long
fn = "c:\test.txt"  '<- file path
delim = vbTab  '<- delimitter
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
MsgBox "There are " & UBound(x) + 1 & " lines"
ReDim a(1 To 65000, 1 To 100)
For i = 0 To UBound(x)
   n = n + 1
   y = Split(x(i), delim)
   For ii = 0 To UBound(y) : a(n,ii + 1) = y(ii) : Next
   myxCol = Application.Max(maxCol, ii - 1)
   If n = 65000 Then
       Sheets(t).Range("a1").Resize(n, maxCol).Value = a
       n = 0 : maxCol = 0 : t = t + 1 : ReDim a(1 To 65000, 1 To 100)
   End If
Next
If n > 0 Then Sheets(t).Range("a1").Resize(n, maxCol).Value = a
End Sub