Find the last Row in an Excel Spread Sheet

[OldDog]

Here we go again. When I get done I will publish an Excel Cookbook for
PowerShell.

In the mean time;

In vbScript;

Const xlCellTypeLastCell = 11

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow

objExcel.Range(strNewCell).Activate

So far in PowerShell;

$xlCellTypeLastCell = 11

$objRange = $z.UsedRange.Item
$objRange.SpecialCells($xlCellTypeLastCell).Activate
$row = $x.ActiveCell.Row
Write-Host "Last Row is: " $row
$strNewCell = "A$row"
$objExcel.Range($strNewCell).Activate

And here is the error:

ERROR: Method invocation failed because [System.Management.Automation.
PSParameterizedProperty] doesn't contain a method named
'SpecialCells'.
ERROR: At line:197 char:24
ERROR: + $objRange.SpecialCells( <<<< $xlCellTypeLastCell).Activate

[Kiron]

> "When I get done I will publish an Excel Cookbook for PowerShell."

:D

$rnd = new-object random
$xlCellTypeLastCell = 11
$xl = new-object -c excel.application
$wb = $xl.workbooks.add()
$sh = $wb.Sheets.item(1)
# add some values
$sh.range('j1:l13') | % {$_.value2 = $rnd.next()}

$xl.visible = 1

$used = $sh.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$newRow = $lastCell.row + 1
$sh.cells.item($newRow,$lastCell.column).value2 = 'This is the new last row
now'

--
Kiron

[OldDog]

Great, thanks Here is my finished snippet

It finds the Grand Total row (which moves around) and uses it for my
chart.

$xlCellTypeLastCell = 11
$used = $z.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row

$row++
$xlBarStacked = 58
$xlDataLabelsShowLabel = 4
$xlDataLabelsShowValue = 2

$xrow = $row - 1
$range = $z.range("E${xrow}:F$xrow")
# create and assign the chart to a variable
$myChart = $x.Charts.Add()
$myChart.ChartType = $xlBarStacked
$myChart.SetSourceData($range)
$myChart.SeriesCollection(1).Name = "Configured Blocks"
$myChart.SeriesCollection(2).Name = "Free Blocks"
[void]$myChart._ApplyDataLabels($xlDataLabelsShowValue)
$myChart.hasTitle = $True
$myChart.chartTitle.text = "TOTAL Managed Blocks"