count total number of lines in big text files and export results for each txt to sheet

Good morning, experts!

I am trying to accomplish the following:


Loop through a given path located in sheet1 on cell G1, get count of lines for each of the text files there for current month and shows those counts in sheet2 in Excel.


The sheet2 needs to look like my image. (See the table)

There maybe instances when a text file was not there on the previous month, I want to add it to the list and put the current count. If the text file was there but is not there this month I want to leave it blank and go to the next text file.


I have some big files (over 133 million records) so this needs to be fast.

This is the code I have now. Currently I have it pointing to sheet1 but needs to go to sheet2. Also, it shows the count for the first file, but I don't know how to show the next files (the loop).

The other caveat is that I need to perform this every month, so there should be another column added in November to be able to put the counts for the November files.


My code:

Dim iRow
Sub ListFiles()

    iRow = 2
    
    'G1 is where the path to the text files is
    Call CountLines(Range("G1"))


End Sub


Sub CountLines(mysourcepath)


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

Set Myobject = New Scripting.FileSystemObject
Set mysource = Myobject.GetFolder(mysourcepath)
On Error Resume Next



'Target File Extension (must include wildcard "*")
myPath = "C:\Path1\Path2\Path3\Text File\"

myExtension = "*.txt"

'Target Path with Ending Extention
  FileNam = Dir(myPath & myExtension)


 'Get Next Available File Handle Number
     FileNum = FreeFile()


     Open myPath & FileNam For Input As #FileNum

         While Not EOF(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
                    
                Loop
                
                

          Wend


 For Each myfile In mysource.Files

        icol = 1
        Cells(iRow, icol).Value = myfile.Name
        icol = icol + 1
        Cells(iRow, icol).Value = myfile.Path
        icol = icol + 1
        Cells(iRow, icol).Value = CountLines - 1
        iRow = iRow + 1

 Next



End Sub

I only manage to make the count for the first file, but for the second it shows the same number as for the first one.

Your help with this will be greatly appreciated.

Thanks a lot in advanced for your time!


T.G

sheet2.jpg