Using VBA to count rows in text file and output results to an excel spreadsheet

Hey,
I am trying to create a small macro that will loop through the text files in the root of a folder (no child folders), and basically just grab the number of rows in each file.. The text files do have a header, but that shouldn't be an issue... I am receiving the error " Data Soruce name not found or No default driver specified"....when connecting to the text file. My code does have the driver specifiedMicrosoft Text driver). have run into this before using VB 6.0, but was able to resolve that issue...I forget what i did.....See below for my code:

Please Login or Register  to view this content.

The bold section is exactly where it is crashing...
I haven't coded far enough to open an excel file and copy the results of how many rows are in each file. Above is just a snippet of my code, as I already have the loop feature working to cycle through each file, just can't figure out this problem...Is it maybe a limitation with excel?


If anyone can point me in right dirrection that would be great. Thanks for your help in advance...

oobern

Have a look at the third tab on the workbook on my download page which has some code to extract the names of files in a folder.

I am able to get the filenames no prob and cycle through each file in a certain folder...
The problem I am having is actually opening each text file and finding the numbers of rows in each...My loop in the code allows me to go through each file, but error happening when trying to open the file using ADO...I get the "no default driver specified" error, which doesn't really make sense since I am directng the connect string to use the Microsoft Text Driver as my driver....

I have done this in VB without a problem but now my VB 6.0 doesn't work with my Vista machine so I am trying to use VBA within excel as my workaround.

I tried using the Open command for Input but that doesn't really seem to work for some odd reason....with that method, I am using a counter to track which row I am on, but for some reason it doesn't stop at end of file, like I direct it to (Do Until EOF().....Loop). I also tried changing the datatype from integer to long and excel just locks up and crashes....Integer should work fine, as each file will definitely have less then the 27000, or 32000 lines - each will have a max of about 20000 records....

I would like to stick with the ADO option if at all possible. Any other suggestions would be greatly appreciated

Will this work? (File names in A1-A5, counts appearing in B1 - B5)

Please Login or Register  to view this content.