Counting rows of data in a closed csv or txt file

[Pavel]

Is it possible to have VBA determine the number of rows of data in a csv or text file without opening the file first? Here is why ...

If Rows > 65,536 Then
Run Macro_A
Else
Run Macro_B
End If

Thanks.

[Leith Ross]

Hello Pavel,

Your reason for not opening the file first must mean that you are using the file Wizard. VBA allows you to Open, Read, Write, and Close files. These is all done in a buffer area separate from Excel. The VBA code gives complete control of what happens with the file's data. Here is a macro to count the Lines in either a CSV or TXT file.

Public Function CountDataLines(ByVal File_Name As String) As Long

Dim Answer
Dim InputData
Dim Lines As Long
Dim FileNumber As Integer

 'Enable error recovery
  On Error GoTo FileProblem

 'Get the file number
  FileNumber = FreeFile

 'Use Open For Input to Read the File
  Open File_Name For Input As FileNumber

   'Loop to Read one line data
    Do While Not EOF(FileNUmber)
      Line Input #1, InputData	
      'Count the Lines Read
       Lines = Lines + 1
    Loop
 
 'Close the File
  Close #1

  'Return the Line Count
   CountDataLines = Lines	
   
 Exit Function 

FileProblem:
  Msg = "An Error Occurred Opening File... " & File_Name & vbCrLf
  Msg = Msg & "  Error Number = " & Err.Number
  Msg = Msg & "  Description: " & Err.Description
  Answer = MsgBox(Msg, vbCritical + VBOkOnly, "Count File Data Lines")
  Err = 0
  CountDataLines = 0

End Function

Copy and Paste this code into a VBA Module.

To Use the Macro:
LinesOfText = CountDataLines "C:\Test File.txt"

Sincerely,
Leith Ross

[Pavel]

Thanks Leith.

I'm a little confused by:

To Use the Macro:
LinesOfText = CountDataLines "C:\Test File.txt"

Can you explain how I make this function a macro?

Thanks.

[Leith Ross]

Hello Pavel,

The function is the macro. A macro is simply a collection of simple commands design to perform a specific task.

Variable(Long) = CountDataLines "Name of file to Open"

The function or macro CountDataLines opens the file and counts the lines of data in the file. The function assigns this count to the Variable.

Hope this helps,
Leith Ross