I am trying to insert a new row into an Excel sheet. I open a file with Powershell, get an object for the sheet, and then get a range object like
$Range=$Sheet.Range("A1")
$Range.Insert()
As I am typing insert, Powershell will even do the tab completion. So, it sees the method there. But when I run that line, I get the error
OperationStopped: Unable to get the Insert property of the Range class
Does anyone know what is going on here, or another way to insert a row? I can't go to the end of text within the worksheet because my data is in a table and there is another table under that. So, it's not that straight-forward.
In order to do so you need to call the table by name:
# Initialize Excel application
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false # Set to $true if you want to see Excel's window
# Open the existing workbook
$Workbook = $Excel.Workbooks.Open("C:\your\excel\path.xlsx")
# Select the worksheet containing the table
$Sheet = $Workbook.Sheets.Item(1)
# Access the table by name (or index if you prefer)
$Table = $Sheet.ListObjects.Item("YourTableName")
# Add a new row at the end of the table
$Table.ListRows.Add() | Out-Null
# Optionally, you can fill data in the newly added row (if needed)
# Let's assume you want to fill the first column of the new row
$LastRowIndex = $Table.ListRows.Count
$Table.DataBodyRange.Rows.Item($LastRowIndex).Columns.Item(1).Value2 = "New Value"
# Save and close the workbook
$Workbook.Save()
$Workbook.Close()
$Excel.Quit()
# Release the COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Table)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)