Exception from HRESULT: 0x800A03EC for Excel in powershell

Question

I am having the worst luck with this line of code...

$Myexcel.visible = $true
$Myworkbook = $Myexcel.workbooks.add()


$Sheet1 = $Myworkbook.worksheets.item(1)
$Sheet1.name = "Summary"


$Sheet1.Cells.item(3, 3) = '=IF(B3>0,ROUND((B3/B16)*100),"N/A")&"%"'

Things I've tried: Shifting/replacing all of the single quotes and double quotes in every configuration you can think of,

escaping all the special characters including the parentheses, the asterisks, the commas, the gt symbol, the percentage symbol, and the configurations of quotes,

and adjusting the scope of the cell (i.e. trying different cells, using a range instead of an item)

I had to make an account just to see if I could get some help on this one, I've spent way too much time trying to get it to work.

Answer 1

tl;dr

The error message isn't very helpful, and seems to be a pretty generic one that is given in a number of situations, but in your case the root cause is the ROUND function takes 2 arguments and you've only specified one.

Try this instead:

$Sheet1.Cells.item(3, 3) = '=IF(B3>0,ROUND((B3/B16)*100,2),"N/A")&"%"'
#                                 add an argument here ^^

Long Version

I initially thought the problem was something to do with the .items part of your sample $Sheet1.Cells.item(3, 3) - I'm more familiar with just using $Sheet1.Cells(3, 3), but they both give the same error, as do the following attempts:

PS> $Sheet1.Cells.item(3, 3) = '=IF(B3>0,ROUND((B3/B16)*100),"N/A")&"%"'
OperationStopped: 0x800A03EC

PS> $Sheet1.Cells(3, 3) = '=IF(B3>0,ROUND((B3/B16)*100),"N/A")&"%"'
OperationStopped: 0x800A03EC

PS> $Sheet1.Cells(3, 3).Formula = '=IF(B3>0,ROUND((B3/B16)*100),"N/A")&"%"'
OperationStopped: 0x800A03EC

So as an experiment I tried manually pasting the formula into a new sheet and got this:

Microsoft Excel

You've entered too few arguments for this function.

[ Ok ]

Your root problem is the ROUND function takes 2 arguments - the value and the precision:

https://support.microsoft.com/en-us/office/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c#:~:text=The%20ROUND%20function%20rounds%20a,of%20this%20function%20is%2023.78.

Syntax

ROUND(number, num_digits)

The ROUND function syntax has the following arguments:

• number Required. The number that you want to round.

• num_digits Required. The number of digits to which you want to round the number argument.

If you change your formula to, e.g.

=IF(B3>0,ROUND((B3/B16)*100,2),"N/A")&"%”

it should work fine.