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:

Please Login or Register  to view this content.

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

Hi,

Replace your Sub CountLines with this one :

Please Login or Register  to view this content.

Hi,

The code you got from internet is assuming that there is always enough memory provided by Excel (I mean the memory provided by Excel, not the available RAM) to hold entire file being read, and since you play with very large files and if one or more of your files are larger than the Excel's memory, an error will be raised.
You can read it here in stackoverflow site : http://www.stackoverflow.com/questio...ray-size-limit

So we must play safe, lets take 450M as a safe value, if the file size is exceeding this value, then we must read it in chunks (as my previous code did).
If you see my previous code, I set the value of FileBufferSize = 50000 (50 KB), because I don't know how big is your files.
I have modify the code to use the maximum array limit size and using array instead of string, it should be much faster now.

Please Login or Register  to view this content.