Append a row above the last row in an excel file using powershell-CodePudding

I have the following code which inserts a row below the row A1000 in my excel file. How could I modify this to insert a row above the last row (will be different each time).

$xlShiftDown = -4121
$File = Get-ChildItem C:\Test.xls
$XL = New-Object -ComObject Excel.Application
$WB = $XL.Workbooks.Open($File.Fullname)
$XL.Visible = $true
$Sheet = $WB.Worksheets.Item('clients')
$objRange = $XL.Range("A1000").EntireRow
[void]$objRange.Insert($xlShiftDown)

CodePudding user response：

Using the Excel ComObject the WorkSheet will have a property called UsedRange that works nicely for things like this.

$objRange = $($Sheet.UsedRange.Rows)[-1]

That should get you the last row of your sheet.

I realized that's probably slow if you have a lot of data. You can instead use the .count property of the UsedRange.Rows, and then just grab that row on the sheet like this:

$objRange = $Sheet.Rows.Item($Sheet.UsedRange.Rows.Count)