CODEWITHSUNDEEP
excelvba
Arun Palanisamy
Arun Palanisamy

Reputation: 5459

Print number of rows in each file inside a folder

I have a folder D:\Arun\myfolder. Inside this say I have 60 files. I want to know the number of rows inside each file like below: (probably written in a separate sheet)

 File1 - 240 rows
 File2 - 321 rows
 File3 - 178 rows
 .. 
 ..

So I'm trying with the below code, but it is not giving me any output. I have very little knowledge in VBA and I'm not sure why the below code is not working.

Sub CountRows()

    Dim wb As Workbook, wbXLS As Workbook
    Dim sPath As String, sFilename As String
    Dim NbRows As Integer, rg As Range
    DisplayAlerts = False
    Set wb = ThisWorkbook
    Application.ScreenUpdating = False
    sPath = "D:\Arun\myfolder"       'Path of XLS Files
    sFilename = Dir(sPath & "*.xls")
    On Error Resume Next
    Do While Len(sFilename) > 0
        If sFilename <> ThisWorkbook.FullName Then
            Set wbXLS = Workbooks.Open(sPath & sFilename)           'open file
            NbRows = wbXLS.Sheets(1).Range("A60000").End(xlUp).Row  'nb of rows
            Set rg = wb.Worksheets("Check").Range("A60000").End(xlUp).Offset(1, 0)
            rg = sFilename
            rg.Offset(0, 1) = NbRows
            wbXLS.Close False   'close file
        End If
        sFilename = Dir
    Loop
    Application.ScreenUpdating = True
    DisplayAlerts = True

End Sub

Upvotes: 0

Views: 140

Answers (1)

Pablo G
Pablo G

Reputation: 199

VBA has methods that make looping through files much easier. Try looping all the .xls files in your folder like this

Sub count_rows()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb, wbXLS As Workbook
Set wb = ActiveWorkbook   'the workbook where you output the data must be active when you run the macro

Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim myfolder As Object
Set myfolder = objFSO.GetFolder("INSERT YOUR FOLDER PATH HERE") 'sets the folder where you have the .xls files to loop

For Each objFil In myfolder.Files
    i = i + 1
    If InStr(1, objFil.name, ".xls") > 0 Then  'you make sure you are only working with .xls files inside your folder
        Set wbXLS = Workbooks.Open(objFil.Path)
        NbRows = wbXLS.Sheets(1).Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Row   'this will count all the cells in column A (it doesn't discriminate blank cells)
        wb.Sheets(1).Cells(i, 1).Value = Replace(objFil.name, ".xls", "")
        wb.Sheets(1).Cells(i, 2).Value = NbRows
        wbXLS.Close False
        Set wbXLS = Nothing
    End If
Next objFil

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Upvotes: 1

Related Questions