CODEWITHSUNDEEP
c#excelexcel-formula
diiN__________
diiN__________

Reputation: 7666

Adding a formula to Excel worksheet results in HRESULT: 0x800A03EC

I've searched for an appropriate solution online but couldn't find anything helpful...

In an Excel worksheet I need to assign some values from a database table and then add a formula to next to each value (depending on another Excel worksheet in the same workbook). Adding the data works perfectly but adding the formula results in an error.

I'm getting the data and adding it to the sheet like this:

using (SqlConnection conn = new SqlConnection("MyConnectionString"))
using (SqlCommand comm = new SqlCommand("SELECT DISTINCT [MyField] FROM [MyTable]", conn)
{
    conn.Open();
    using (SqlDataReader reader = comm.ExecuteReader())
    {
        myStringList.Add("MyField");
        if (reader.HasRows)
            while (reader.Read())
                myStringList.Add(reader.GetString(reader.GetOrdinal("MyField")));
    }
}

workbook.Worksheets.Add(After: workbook.Worksheets[workbook.Sheets.Count]);

for (int counter = 1; counter <= myStringList.Count(); counter++)
    ((Excel.Worksheet)workbook.ActiveSheet).Cells[counter, 1] = myStringList[counter-1];

So far so good. Now I get to my problem. I need to add a formula to the cells B2, B3, ... for every used cell in column A. The difficulty is that I want to do it with a for loop because the formula depends on the column A.

for (int counter = 2; counter <= myStringList.Count(); counter++)
    ((Excel.Worksheet)workbook.ActiveSheet).Range["B" + counter].Formula
        = $"=VLOOKUP(A{counter};MyOtherWorksheet!$B$2:$B${numberOfRows};1;FALSE)";

numberOfRows is the number of rows in column B in MyOtherWorksheet (it returns the correct number in debugger, so that's not the problem).

But when I assign the formula like this, I'm getting the following exception without any helpful message:

HRESULT: 0x800A03EC

I tried changing .Range["B" + counter] to .Cells[counter, 2] and even tried using .FormulaR1C1 instead of .Formula but I got the same exception.

What am I missing?

Upvotes: 3

Views: 1095

Answers (1)

diiN__________
diiN__________

Reputation: 7666

I've found the problem. I had to change .Formula to .FormulaLocal.

MSDN description for .FormulaLocal:

Returns or sets the formula for the object, using A1-style references in the language of the user. Read/write Variant.

MSDN description for .Formula:

Returns or sets a Variant value that represents the object's formula in A1-style notation and in the macro language.

Upvotes: 9

Related Questions