C# - Set excel cell with a formula containing double quotes

1
votes

I'm trying to set via c# code the formula of an excel cell. I use Microsoft.Office.Interop.Excel, version 14. I always get an excel error 0x800A03EC, which is a kind of generic error.

String formula = "=IF(A2=\"BLANCO\";\"\";C1+1)"    

Range cell = (Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 3];
cell.Formula = formula;

I also tried to escape the double quotes with an @

String formula = @"=IF(A2=""BLANCO"";"""";C1+1)"

Same error, same problem.

When I try to set a simple formula, where no quotes are involved it is working fine.

Anybody has a solution?

2
c#excelexcel-formuladouble-quotes
Shouldn't the formula be @"=IF(A2=""BLANCO"","""",C1+1)" (commas not semicolons)? – D Stanley
D Stanley is right, use commas in the formula not ; – Jon
this solution solved the problem for my code : stackoverflow.com/a/41951868/649825 – PetersLast

2 Answers

2
votes

Don't have much context for your code, but slashes should work when used appropriately. I am not sure about the use of semicolons here.

This code is an example pulled straight from one of my Interop programs and works fine: thisExcel.xlWorksheet.Range["AD44", Type.Missing].Value = "=IF(BULK!L7=\"#N/A Field Not Applicable\",\"( \"&'Title Look Up'!C3&\" )\",\"( \"&'Title Look Up'!C3&\" )\")";

If all you want is: 'if a2 reads blanco then nothing else increment value of c1 by 1', then you just need: =IF(A2=\"BLANCO\",\"\",C1+1)

I would also try using .Value rather than .Formula.

Hope that help

0
votes

Building upon @getglad's answer - Using a slash does work for double quotes.

My recent solution (as of 2020):

worksheet.Cells[row,col].Value = "=IFERROR(VLOOKUP(etc),\"\")";