Fetch File statistics including rowcount

[Tarana]

Hi,

I have a VBA code which will fetch statistics of files placed under a folder (including size, path, date create/ modified). Code is working perfectly, I want to modify it in such way that it should fetch rowcount for each file. It would be helpful if you can help me in that. I am stuck in this one.
Please find the code below:
'Force the explicit delcaration of variables
Option Explicit

Sub ListFiles()

'Set a reference to Microsoft Scripting Runtime by using
'Tools > References in the Visual Basic Editor (Alt+F11)

'Declare the variables
Dim objFSO As Scripting.FileSystemObject
Dim objTopFolder As Scripting.Folder
Dim strTopFolderName As String

'Insert the headers for Columns A through F
Range("A1").Value = "File Name"
Range("B1").Value = "File Size (KB)"
Range("C1").Value = "File Type"
Range("D1").Value = "Date Created"
Range("E1").Value = "Date Last Accessed"
Range("F1").Value = "Date Last Modified"
Range("G1").Value = "File Path"
Range("H1").Value = "File rowcount"

'Assign the top folder to a variable
'Change the folder as required
strTopFolderName = "C:\Users\Desktop\Reusable components\Test"

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the top folder
Set objTopFolder = objFSO.GetFolder(strTopFolderName)

'Call the RecursiveFolder routine
Call RecursiveFolder(objTopFolder, True)

'Change the width of the columns to achieve the best fit
Columns.AutoFit

End Sub

Sub RecursiveFolder(objFolder As Scripting.Folder, _
IncludeSubFolders As Boolean)

'Declare the variables
Dim objFile As Scripting.File
Dim objSubFolder As Scripting.Folder
Dim NextRow As Long

'Find the next available row
NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

'Loop through each file in the folder
For Each objFile In objFolder.Files
Cells(NextRow, "A").Value = objFile.Name
Cells(NextRow, "B").Value = objFile.Size
Cells(NextRow, "C").Value = objFile.Type
Cells(NextRow, "D").Value = objFile.DateCreated
Cells(NextRow, "E").Value = objFile.DateLastAccessed
Cells(NextRow, "F").Value = objFile.DateLastModified
Cells(NextRow, "G").Value = objFile.Path
'Cells(NextRow, "H").Value =
NextRow = NextRow + 1
Next objFile

'Loop through files in the subfolders
If IncludeSubFolders Then
For Each objSubFolder In objFolder.SubFolders
Call RecursiveFolder(objSubFolder, True)
Next objSubFolder
End If

End Sub

 

[Norie]

The rowcount of what exactly?

Each sheet in the workbook?

Each range/table in the workbook?

 

[Tarana]

from this code, I will be getting a bunch of excel/ csv files (lets say 10). I want rowcount of each of those 10 files.

The rowcount of what exactly?

Each sheet in the workbook?

Each range/table in the workbook?

 

[Norie]

Are they always single sheet Excel files or CSV files?

 

[Tarana]

Are they always single sheet Excel files or CSV files?

Yes, only one worksheet in all files.