VBA Macro to Count Rows across Multiple Workbooks

I have found from google a script to open csv files in a directory count the rows. Then it is supposed to print in the workbook with the macro the file name and the amount of rows. Then close the csv file it opened and move on to the next csv file in the directory until there is no more. It stops at opening the first csv file and doesn't do anything past that. I have not worked much with VBA, so I am stuck, and anything that I try still doesn't seem to get past of just opening the first file.

Here is the code:

[VBA]
Sub OpenCSVFiles()

Dim wb As Workbook, wbCSV As Workbook
Dim sPath As String, sFilename As String
Dim NbRows As Integer, rg As Range

Set wb = ThisWorkbook

Application.ScreenUpdating = False

sPath = "C:\Users\ncurran\Desktop\asdf" 'Path of CSV Files
sFilename = Dir(sPath & "*.csv")

Do While Len(sFilename) > 0
Set wbCSV = Workbooks.Open(sPath & sFilename) 'open file
NbRows = wbCSV.Sheets(1).Range("A100").End(xlUp).Row 'nb of rows

Set rg = wb.Sheets(1).Range("A100").End(xlUp).Offset(1, 0)
rg = sFilename
rg.Offset(0, 1) = NbRows

wbCSV.Close False 'close file
sFilename = Dir

Loop
Application.ScreenUpdating = True

End Sub
[/VBA]

It seemed to work with a really small file, but my actual files that have over 100,000 rows in them it seems to not continue after opening the first file.

Untested but give it a try
[VBA]Sub OpenCSVFiles()

Dim wb As Workbook, wbCSV As Workbook
Dim sPath As String, sFilename As String
Dim NbRows As Integer, rg As Range

Set wb = ThisWorkbook

Application.ScreenUpdating = False

sPath = "C:\Users\ncurran\Desktop\asdf\" '\ added to correct file path
sFilename = Dir(sPath & "*.csv")

Do While Len(sFilename) > 0
Set wbCSV = Workbooks.Open(sPath & sFilename) 'open file
NbRows = wbCSV.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'maximise rows to check; 100 may be exceeded

Set rg = wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'maximise result rows; 100 may be exceeded
rg = sFilename
rg.Offset(0, 1) = NbRows

wbCSV.Close False 'close file
sFilename = Dir

Loop
Application.ScreenUpdating = True

End Sub
[/VBA]

With that, I get a Run-time error '6': Overflow.

I got it!

I changed:

[VBA]Dim NbRows As Integer[/VBA]

to

[VBA]Dim NbRows As Long[/VBA]

And it worked perfectly fine!

Hello All, could this script be utilized for similar purposes, but with .txt or .xlsx files?
If so, are there any modifications to be made and please, what would the modifications be?

Thanks!

Quote:

---

Originally Posted by ncurran217

I got it!

I changed:

[VBA]Dim NbRows As Integer[/VBA]

to

[VBA]Dim NbRows As Long[/VBA]

And it worked perfectly fine!

---