PDA

View Full Version : VBA Macro to Count Rows across Multiple Workbooks



ncurran217
04-24-2013, 12:22 PM
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:


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

ncurran217
04-24-2013, 12:35 PM
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.

mdmackillop
04-24-2013, 03:23 PM
Untested but give it a try
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

ncurran217
04-24-2013, 06:04 PM
With that, I get a Run-time error '6': Overflow.

ncurran217
04-24-2013, 06:06 PM
I got it!

I changed:

Dim NbRows As Integer

to

Dim NbRows As Long

And it worked perfectly fine!

Bdeeve
08-20-2019, 08:03 AM
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!


I got it!

I changed:

Dim NbRows As Integer

to

Dim NbRows As Long

And it worked perfectly fine!