Need a PowerShell/VBS script to Count No# of rows in Mutiple Excel spreadsheets

Here's what I found from the Scripting Guy which I then modified to create your script request.

http://blogs.technet.com/b/heyscriptingguy/archive/2010/04/05/hey-scripting-guy-april-5-2010.aspx

$path = "C:\fso"
$excelSheets = Get-Childitem -Path $path -Include *.xls,*.xlsx -Recurse
$excel = New-Object -comobject Excel.Application
$excel.visible = $false
"Name;Rows" >> "RowTotal.txt"
foreach($excelSheet in $excelSheets)
{
 $workbook = $excel.Workbooks.Open($excelSheet)
 #"There are $($workbook.Sheets.count) sheets in $excelSheet"
$RowTotal = 0
 For($i = 1 ; $i -le $workbook.Sheets.count ; $i++)
 {
  $worksheet = $workbook.sheets.item($i)
  $rowMax = ($worksheet.usedRange.rows).count
  $columnMax = ($worksheet.usedRange.columns).count
$RowTotal += $rowMax

  $worksheet = $rowmax = $columnMax = $row = $column = $formula = $null
 } #end for
"$($excelsheet.fullname);$RowTotal" >> "RowTotal.txt"
 $workbook.saved = $true
 $workbook.close()
} #end foreach
$excel.quit()
 $excel = $null
 [gc]::collect()
 [gc]::WaitForPendingFinalizers()

Select allOpen in new window

Note: it doesn't create a CSV but you can easily export into CSV in the script, or like I prefer: importing into an excel, separated by semicolon.  Just open the excel, click on the Data Tab, click on From Text, and just follow the wizard.

Regards,

DH

Subsun, your powershell script perfect ... The output is what I am looking for thank you for your help.....

Amstoots,

Did you happen to see my response as well?

DH

yes I did DaleHarris , sorry I didn't respond to yours.... Subsun Powershell script was more streamlined and and was able to use Export-CSV to output the Resolute. I did attempt to use your but ran in few problems... thank you for your time you have put in this.......

Okay no problem.  Glad you got it solved quickly.

I have one more question on this script guys...... How can you tell the script to look for a pacific name in a list of spreadsheets.... For example, I need to look for number of spreadsheet that have the following file name "Local Administrator Access by All Servers MM-DD-YYYY.xls". I would like to add to the script to only search for those files in the folder with the file name "Local Administrator Access by All Servers MM-DD-YYYY.xls" and use a wild card "*" for the date (i.e. Local Administrator Access by All Servers *.xls.) Would this be possible.....?

Yes you can do that..

Get-Childitem -Include "Local *.xls","Local *.xlsx" -Recurse

Select allOpen in new window

Perfect, thanks for the additional Help.... Subsun  .....  :)

You are welcome!!

Hi,

Thank you subsun for posting this.. It worked accurately on most files but its is displaying the wrong number for a couple of them. Any idea as to why?