Delete first four rows in Excel with Powershell

Question

I have a powershell script that opens an excel file, re-assigns a blank password, and then saves the file. I want to add a task to the script to remove the first 4 rows of the excel file before saving it.

Answer 1

You can't use OleDB for deleting data from Excel document. As per MSDN docmentation:

Although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow DELETE operation

What you can do is use Exel's COM interface to delete rows. Remember to release COM object too. Like so,

$file = c:\temp\MyExcelFile.xls
# Star Excel, hide window
$excel = new-object -com Excel.Application -Property @{Visible = $false} 
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $workbook.Sheets.Item(1) # Activate the first worksheet
[void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row

$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM