Exception from HRESULT: 0x800A03EC when setting value of a cell (through Powershell)

Question

I am trying to set a value to an excel cell through Powershell, and I am getting the error HRESULT: 0x800A03EC, which ends the script prematurely. I realize there have been other questions relating to this error or similar, but none of the solutions have worked for me, so I am assuming this is a separate problem.

I have run my script before but it is only now giving me this error.

Relevant code:

$Output_Location = "Z:\Documents\Powershell"
$Excel_File = "Report.xlsx"
$ExcelWorkBook = $Excel.Workbooks.open("$Output_Location\$Excel_File")
$MainSheet = $ExcelWorkBook.worksheets.Item("Report")
$Sheet1 = $ExcelWorkBook.worksheets.Item("Sheet1")
$Sheet1.name = "Statistics" 
$StatisticsSheet = $ExcelWorkBook.worksheets.Item("Statistics")

$row = 3
$column = 2
$StatisticsSheet.Cells.Item(2,2)= 'KeyToMatch'
$StatisticsSheet.Cells.Item($row,$column) = '=COUNTIFS(Report!E2:E200000,B$3,Report!G2:G200000,"UserMailbox")'
$row++
$StatisticsSheet.Cells.Item($row,$column) = '=COUNTIFS(Report!E2:E200000,B$3,Report!G2:G200000,"RemoteUserMailbox")'
$row++

The code loads up the excel file and hits the line which sets the cell (2,2)/(B,2) to its value just fine. But when the code hits the line setting the cell value the row below the KeyToMatch (B,3), it throws the error 0x800A03EC.

Full error:

Exception from HRESULT: 0x800A03EC
At Z:\Documents\Powershell\Reporting\Report.ps1:113 char:1
+ $StatisticsSheet.Cells.Item($row,$column).value = '=COUNTIFS(Report! ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

I have tried spacing out the '=' between the cell and value, I have also tried the following:

$StatisticsSheet.Cells.Item($row,$column).value = ...
$StatisticsSheet.Cells.Item($row,$column).value2 = ...
$StatisticsSheet.Cells.Item($row,$column).text = ...
$StatisticsSheet.Cells.Item($row,$column).formula = ...

I can comment out any number of lines which set the cell's value to a formula and the first one to attempt to do so will throw the mentioned error.

Like I said, I have run this script before but it is only now giving me troubles. How can I fix this so the code runs smoothly?

Answer 1

Moved solution from question to answer:

RESOLUTION:

To resolve this issue, in the text I assigned to the cell, I replaced single quotes with double quotes, and because of this, had to escape the '$' and ' " ' characters. I also ADDED single quotes around "Report" (The table name from which data is being pulled) each time it came up within the text.

It ended up looking like this, and running fine:

$StatisticsSheet.Cells.Item($row,$column) = "=COUNTIFS('Report'!E2:E200000,B`$3,'Report'!G2:G200000,`"UserMailbox`")"

Still not sure why this error occurred-the code had worked every week prior to this! Before changing the script and finding the resolution, I tested it on multiple machines (5+) and it threw the error mentioned in the title every single time.