Counting the number of rows in multiple Excel files

Tonight's Tip of the Night is for Riley who contacted me a few days ago asking for a way to count the number of pages in multiple Excel files, in a way similar to the method demonstrated in the Tip of the Night for April 11, 2017 for counting the number of pages in multiple PDF files. That method used Visual Basic code. Tonight's tip involves a Powershell script, which was posted to Experts' Exchange by Subsun. The tip counts the number of rows, not pages. The number of pages in an Excel file can vary widely depending on how it is formatted.

Open PowerShell ISE (x86) and paste it in the script pane at the top. Enter the path to the folder containing your Excel files at the beginning, and also enter a path for the directory you want to contain the report that will be generated. Then simply press play on the toolbar to run the script. Each Excel file will be opened, and the number of rows will be listed in the blue console at the bottom, as well as being listed in the .csv file script generates.

Clear-Host $Report = "C:\FooFolder\excels\report.csv" $path = "C:\FooFolder\excels" [Array]$Results = $null $excelSheets = Get-Childitem -Path $path -Include *.xls,*.xlsx -Recurse $excel = New-Object -comobject Excel.Application $excel.visible = $false

foreach($excelSheet in $excelSheets) { $workbook = $excel.Workbooks.Open($excelSheet) $rowCount = $null For ($i = 1 ; $i -le $workbook.Sheets.count ; $i++) { $worksheet = $workbook.sheets.item($i) $rowMax = ($worksheet.usedRange.rows).count $rowCount += $rowMax } $Results += New-Object Psobject -Property @{ "File Name"=$excelSheet.Name "Sheet Count"=$workbook.Sheets.count "Row Count"=$rowCount} $excelSheet.Name $workbook.Sheets.count $rowCount } $excel.quit() $Results | select "File Name","Sheet Count","Row Count" | Export-Csv $Report -NoTypeInformation