Name and row count for every .xls file in a directory

[johnpmx]

I have a set of excel files in a directory, is it possible to print every file name and the number of rows in each file through a vba script? if yes, could some one please help me.

[mancubus]

wellcome to vbax

by number of rows i understand the number of rows in active sheet, ie the worksheet the the workbook last saved.

you may try this.

[vba]
Sub OpFileCntRow()

Dim wb As Workbook, ws As Worksheet
Dim rng As Range
Dim fFile As String, fPath As String
Dim lrow As Long, cntRows As Long

fPath = "C:\Users\MyName\Docs\"
fFile = Dir(fPath)

Set wb = ThisWorkbook 'change to suit
Set ws = wb.Worksheets("Sheet1") 'change to suit

With ws
.Range("A1") = "File Name"
.Range("B1") = "Number of Rows"

lrow = 1
Do Until fFile = ""
lrow = lrow + 1
Cells(lrow, 1).Value = fFile
fFile = Dir
Loop

Set rng = .Range("A2")
While rng.Value <> ""
Workbooks.Open fPath & rng.Value
cntRows = ActiveSheet.UsedRange.Rows.Count
ActiveWorkbook.Close False
rng.Offset(0, 1) = cntRows
Set rng = rng.Offset(1, 0)
Wend
End With

End Sub
[/vba]