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.
Add a comment
|
1 Answer
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
2 Comments
Lorna Robinson
Hi, Perfect, TY! Would you happen to know how to use a dynamic file name in the same script...ends with "mmddyyyy"?
vonPryz
You should post the part about dynamic file name as a question of its own. Provide more info about how the name is going to change and what should one do to the file(s) after removing the 1st row.