How To Count The Number Of Rows Without Opening An Excel File!

[Sammy]

This might sound silly/crazy/plain laziness . I have 30-40 .csv files that I open using Excel, each containing some numbers in row 'A'. Is there any way that we can take a count of the number of rows in each file and have it updated in a separate workbook?


What I am doing right now is opening the file one after the other and taking the count by pressing the shortcut keys "CTRL+Space Bar" ( The file always has the active cell as 'A1', which is time consuming


Note: The workbook names are different.

 

[GCExcel]

Hi Sammy,


Try this code in a new workbook, update the path where your CSV files are and run the macro. The name of each files will be written in Column A of sheet 1 of your new workbook. In column B, the number of rows of each file.

[pre]

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:UsersXXXDesktopYourCSVFolderHere"       '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("A60000").End(xlUp).Row  'nb of rows

Set rg = wb.Sheets(1).Range("A60000").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

[/pre]

 

[Sammy]

Thank you, GCExcel. Worked like wonders... fabulous... Now I have some time left for me to read more of Chandoo's and learn more

 

[Sammy]

Thank you, GCExcel. Worked like wonders... fabulous... Now I have some time left for me to read more of Chandoo's and learn more VERY VERY MUCH APPRECIATED...

 

[Bhupathi]

Hi,


I am new to Excel Macros... may I know if this code also works for for Excel files (.xlsx) instead of .csv?


Also I am not sure how to run the Macro from an excel work book...appreciate your help on this?

 

[DaveTurton]

It should work fine - change

sFilename = Dir(sPath & "*.csv") to sFilename = Dir(sPath & "*.xlsx")

The code needs to be put into a module in a VBA project. In this case, I imagine in a another workbook.


Open a blank workbook. Press Alt and F11. go to Insert - module. Paste the code in.


To run it you can pres Alt and F8 then select the macro and press Run. Or you can assign the macro to a botton using the forms toolbar.

 

[Bhupathi]

Also... I would like to know how I can make it work to read through the sub directories inside the directory;