Trying to count Excel Rows in Powershell

I don't think that you can get the number of lines without actually opening the file in Excel. As such you are going to need to choose if you want this to be fast, or if you want it to be accurate. Do your files include header rows, and should those be included?

Getting the UsedRange for a file is easy once it's open in Excel, and opening files in Excel is simple enough really. The UsedRange is not always completely accurate of the actual range that has data, but more often than not it's a good indication of it. It is possible to format cells beyond those that have data, and in that case your UsedRange will include those 'blank, yet formatted' cells. If that isn't a concern then your issue is simple.

What I'll use for this will be Get-ChildItem, just like you did, to get the actual files. I'll also be using the Excel ComObject to open the program and interact with it (open files, get info, close files). I'll open each file and get the UsedRange for the last active worksheet of the file, and grab the count of the rows property. Then we will use Add-Member to append that info to the [FileInfo] object that we got from Get-ChildItem, and collect that info in a variable.

$Excel = New-Object -comobject Excel.application
$ExcelFiles = Get-ChildItem .\*.xlsx -recurse | ForEach{
    $WB = $Excel.Workbooks.Open($_.fullname)
    Add-Member -InputObject $_ -NotePropertyName 'RowCount' -NotePropertyValue ($Excel.ActiveSheet.UsedRange.Rows.Count) -PassThru
    $WB.Close()
}
$Excel.Quit()

From here you can output the information you now have however you'd like. For example, just the name of the file, and the number of rows:

$ExcelFiles | Format-Table Name,RowCount