2

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?

Bugs
  • 4,491
  • 9
  • 32
  • 41
Alex C. Fish
  • 83
  • 2
  • 6
  • Your code works fine for me on PowerShell 5.1 and Excel 2013. What versions are you using? Have you checked out [this post](https://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range)? I see you have `.xlsx` in your code but that's not necessarily the file format. Try with a range of `E2:E2000` to see if same error occurs. Also, `$Excel.Visible = $true` may be helpful for debugging. – G42 Jul 11 '17 at 17:20
  • @gms0ulman I am using Powershell 5.0.10586.117 and Excel 2013. When I select properties on the (unfinished) automated excel file (after the program exits), it says file type of .xlsx. I use $Excel.visible = $true but did not include it to save clutter in my question - which is how I know the program partially completes the automation. I followed the steps in the question you linked but got the same result when I tested again. Any idea as to why it throws this error on my system? – Alex C. Fish Jul 11 '17 at 17:38
  • @gms0ulman I tested with range E2:E2000 and it threw the same error on the same line. – Alex C. Fish Jul 11 '17 at 17:57

2 Answers2

2

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.

Community
  • 1
  • 1
Bugs
  • 4,491
  • 9
  • 32
  • 41
-1

this is very simple issue, which in range put the values like

ExcelSheet.Cells.Range("A1:A1").Value = "'asdf"
Nic3500
  • 8,144
  • 10
  • 29
  • 40
yogesh ballewar
  • 1