OperationStopped with copying excel worksheet to another excel file in Powershell

0

I have an automation Powershell script running for years. But in the running this month, it keeps reporting errors when tried to copy an Excel sheet to another Excel file

$excel = new-object -comobject excel.application
$excel.Visible = $True
$excel.EnableEvents=$False
$excel.DisplayAlerts=$False

....

48   #Copy Summary tab from each model to Golden Summary File
49   $sheetToCopy = $workbook.Sheets.Item("Scenario") 
50   $summary_sh1 = $summarywb.Sheets.Item(1)
51   $sheetToCopy.Copy($summary_sh1)

The error message was

Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))
At Z:\ScenarioRun.ps1:49 char:1
+ $sheetToCopy = $workbook.Sheets.Item("Scenario")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

You cannot call a method on a null-valued expression.
At Z:\ScenarioRun.ps1:51 char:1
+ $sheetToCopy.Copy($summary_sh1)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

My script was running on Win10&Office2010. I also tried to run it on Win10&Office365 platform and got same errors. Since it worked quite well before this month, I am not sure whether there was Windows or Office upgrade on Microsoft side or changes on my company's IT configuration side.

I have a loop coding to run this logic for all Excel files in certain folder. The logic did not fail every time. It is like reported 3 failures while the other 2 succeeds. And the failure of prior excel copy() would not impact the success of excel copy() of following files.

Any insight or input is appreciated.

Thanks. Leal

excel
powershell
asked on Stack Overflow Jun 19, 2020 by Leal

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0