I used the PowerShell script below in the Run .Net Script activity.
Script
#Remove Worksheets
$objExcel = new-object -Com Excel.Application
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$objExcel.Visible = $false
$objExcel.displayalerts = $false
$objWorkbook = $objExcel.Workbooks.Open("\\PathtoMyFile\MyFile.xlsx")
$objWorksheets = $objWorkbook.Worksheets | Where {($_.Name -ne "Data")}
If ($objWorksheets) {
ForEach ($objWorksheet in $objWorksheets) {
$objWorksheet.Delete()
}
}
$objWorkbook.Save()
$objExcel.Workbooks.Close()
$objExcel.quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objWorkbook)
Remove-Variable objExcel
Remove-Variable objWorkbook
Running this script manually on the runbook server worked fine and as expected. However, running the script in SCOrch throws the following error (or similar error with opening the file).
Error
Exception calling "Open" with "1" argument(s): "Microsoft Excel cannot access the file '\\PathtoMyFile\MyFile.xlsx'. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook."
To solve this issue, create an empty folder called "Desktop" under the systemprofile folder as shown below:
C:\Windows\SysWOW64\config\systemprofile\Desktop
C:\Windows\System32\config\systemprofile\Desktop
