Count non-empty cells in Excel using PowerShell-CodePudding

I need to know the number of non-empty cells in Excel using PS script.

$excel = new-object -comobject excel.application
$excel.visible = $false
$Workbook = $excel.workbooks.open($FileDir)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(1)
$Range1 = $Excel.Range("8:8")
$Range1.Count
$Workbook.Save()
$excel.Quit()

This code gets me 16384 which is total number of columns in Excel.

Excel function COUNTA() works in excel and gets me 109 which is the correct result.

I probably need this https://docs.microsoft.com/en-gb/office/vba/api/excel.worksheetfunction.counta but my implementation doesn't work. $Range1.WorksheetFunction.CountA("")

CodePudding user response：

WorksheetFunction is a property of the Excel.Application class, not the Excel.Range class, so your code needs to look something like this:

$excel = new-object -comobject Excel.Application;

# set up some dummy data
# note - we'll leave A4 blank
$workbook = $excel.Workbooks.Add();
$worksheet = $workbook.Worksheets.Item(1);
$worksheet.Range("A1").Value = "a"
$worksheet.Range("A2").Value = "b"
$worksheet.Range("A3").Value = "c"
#$worksheet.Range("A4").Value = "d"
$worksheet.Range("A5").Value = "e"

$range = $worksheet.Range("A1:A5");

$count = $excel.WorksheetFunction.CountA($range);
#        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

write-host $count
# 4

Instead of trying to invoke WorksheetFunction on the range, you invoke it from the application and pass the range as a parameter.