CODEWITHSUNDEEP
excelpowershellexcel-2007excel-2010powershell-2.0
sion_corn
sion_corn

Reputation: 3151

disable Excel Compatibility Checker when saving/closing .xls file via PoSh

I am using a PoSh script to open/edit/save/close a .xls file. When the file opens, I am pretty sure it is handled by Excel 2010.

I am using the script found here, with no changes made other than pointing to my file path.

It works like I need it to work, but when it is in the save/close phase of the process, the Excel Compatibility Checker appears, and I have to click 'Ok' for it to commit.

Is there any way to disable the Compatibility Checker in the PoSh script?

Upvotes: 1

Views: 3050

Answers (2)

Flo
Flo

Reputation: 11

I have tried adding $workbook.CheckCompatibility = $False but with no succes. Solved this by adding this line also: $Excel.displayalerts = $false

Upvotes: 1

sion_corn
sion_corn

Reputation: 3151

I figured it out.

I added the following line after opening the workbook: $workbook.CheckCompatibility = $False

The script in full is as follows:

$excel = new-object -com Excel.Application -Property @{Visible = $false} 
$workbook = $excel.Workbooks.Open($file) # Open the file
$workbook.CheckCompatibility = $False
$sheet = $workbook.Sheets.Item("Paste value") # Select appropriate 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

I love PowerShell!

Upvotes: 1

Related Questions