Dailycode.info

Short solution for short problems

Inserting functions from .net into Excel. (VLOOKUP) "Exception from HRESULT: 0x800A03EC"

I got a small task to write a program that takes single language excel sheets and makes them multilanguage.
To accomplisch this we added a translation worksheet that will be filled with all translations of text in de excel worksheets for 4 different languages.
Then the text in the excel will be replaced with a VLOOKUP function that looks up the string in the Translation worksheet.
The program was ready in half a day, but then Excel strarted giving me a hard time.
If I tried to insert the VLOOKUP functino from code, it kept giving me an error. Inserting strings or even a SUM function was no problem, but the VLOOKUP wouldn't work. The functino looks like this: =VLOOKUP("Bath / Object";TRANSLATIONS;SPRACHE;FALSE).
This code was trying to insert the code in the cell where the text Bath / Object was:
 
xlApp.Worksheets.get_Item(index).Select();
string value = String.Format(@"=VLOOKUP(""{0}"";TRANSLATIONS;SPRACHE;FALSE)", str);
rangeSet.FormulaR1C1 = value;
First I tried all kinds of things to replace the quotes, but it didn't help. I tried setting the Value or Value2, Formula etc but it kept ginving me the error. Finally after a long search I recorded a macro in Excel and edited this macro and swaw that the Macro was using comma's (,) instead of colons (;).
So I changed the code looking like this and it works!!!!
xlApp.Worksheets.get_Item(index).Select();
string value = String.Format(@"=VLOOKUP(""{0}"",TRANSLATIONS,SPRACHE,FALSE)", str);
rangeSet.FormulaR1C1 = value;
The Nice error I got was: Exception from HRESULT: 0x800A03EC. Try to figure this out!