count the rows in multiple file

[freez]

Hi
i search the forum but nothing found..
i have about 50 excel file in a folder in average of 30 Mb
i need to have number of rows in each file with file name in separated file
like this:

a.xls 51229
b.xls 49491
c.xls 44
.
.
.


what should i do?
macros can do this?
(also if i can have columns count it's seems better )
Thanks

 

[Sektor]

You need to count number of rows in ALL sheets or only the first one?

 

[freez]

You need to count number of rows in ALL sheets or only the first one?

Hi
all of files have just one sheet
so the answer is the first one

 

[Sektor]

It'd be better if you'd upload an excerpt of your real data in one file. Without a file, I would advice to use UsedRange.Rows count, but this method is not that reliable.

 

[freez]

the problem is that i don't know VBA
the file is very simple excel with columns filed with numbers and strings
if it's nesscery to upload?

 

[Sektor]

Yes, upload. Data mist be real.

 

[Sektor]

I've sent you workbook with a button inside. The macro collecting data is simple:

Sub CollectData()

    Dim fso As Object, xlFile As Object
    Dim sFolder$
    Dim r&, j&

    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path
        If .Show Then sFolder = .SelectedItems(1) Else Exit Sub
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each xlFile In fso.GetFolder(sFolder).Files
        With Workbooks.Open(xlFile.Path)
            With .Sheets(1)
                j = .Cells(.Rows.Count, 1).End(xlUp).Row
            End With
            .Close False
        End With
        r = r + 1
        Cells(r, 1).Value = xlFile.Name
        Cells(r, 2).Value = j
    Next

End Sub

 

[freez]

work fine,
thank you

 

[Sektor]

You're welcome!