rajeev.harbola rajeev.harbola - 2 months ago 25
C# Question

Exception from HRESULT: 0x800A03EC INDIRECT

I'm trying to run excel indirect function from c#. I used the following code but got the exception


"A first chance exception of type
'System.Runtime.InteropServices.COMException' occurred in
System.Dynamic.dll"


Additional information: Exception from HRESULT: 0x800A03EC

Excel.Range vehicle_makes_dropdown = xlWorkSheet1.get_Range("B2", "B101");
vehicle_makes_dropdown.Formula = "=indirect(A2)";
vehicle_makes_dropdown.Validation.Add(Excel.XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, XlFormatConditionOperator.xlEqual, vehicle_makes_dropdown.Formula, misValue);

vehicle_makes_dropdown.Validation.IgnoreBlank = true;
vehicle_makes_dropdown.Validation.InCellDropdown = true;


Updated Code is

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wlb = app.Workbooks.Open(@"D:\Templates2.xlsx");
Microsoft.Office.Interop.Excel.Worksheet MySheet = (Microsoft.Office.Interop.Excel.Worksheet)wlb.Sheets[1];


Microsoft.Office.Interop.Excel.Range oRange;

// Get Subject



// string subjetcs = string.Empty;
//List<Topics> lstsubject = PickSubject(out subjetcs);
AdminQuestionCommonModel objAdminQuestioncommonModel = new AdminQuestionCommonModel();
// _Question.BindQuestionDropDowns(objAdminQuestioncommonModel);
objAdminQuestioncommonModel.ExcelTopics = _Question.GetTopics(subjectId);

List<SubjectBO> lstTopics = GlobalService.GetAllTopicsandSubtopics(5, subjectId, 0); // get all topics of Subjects
//if (lstsubject.Count > 0)
if (lstTopics.Count > 0)
{
Microsoft.Office.Interop.Excel.Worksheet IDSheet = (Microsoft.Office.Interop.Excel.Worksheet)wlb.Sheets[2];


int rows = 1;
int subrows = 1;
IDSheet.Unprotect("123#");


//foreach (var item in lstsubject)
foreach (var Topic in lstTopics)
{

var TopicName = Topic.Topic_SubTopic.Trim();
TopicName = TopicName.Replace(".", "_").Replace("&", "_").Replace(" ", "_").Replace("__", "_");

// Add the header the first time through
IDSheet.Cells[rows, 1] = Topic.SubjectTopicId.ToString();
IDSheet.Cells[rows, 2] = TopicName;

// List<SubTopics> lst = PickSubTopic(item.TopicName, item.TopicID);
List<SubjectBO> lstsubtopics = _Question.GetSubTopics(subjectId, Topic.SubjectTopicId);

int startindex = subrows;
foreach (var subtopics in lstsubtopics)
{
IDSheet.Cells[subrows, 4] = subtopics.Topic_SubTopic;
IDSheet.Cells[subrows, 5] = subtopics.SubjectTopicId.ToString();
IDSheet.Cells[subrows, 6] = TopicName;
IDSheet.Cells[subrows, 7] = Topic.SubjectTopicId.ToString();
subrows++;
}
if (lstTopics.Count > 0)
{
wlb.Names.Add(TopicName, IDSheet.get_Range("D" + startindex + ":D" + (subrows - 1)));
}
subrows++;
rows++;

}


wlb.Names.Add("Topics", IDSheet.get_Range("B1:B" + (rows - 1)));

Microsoft.Office.Interop.Excel.Range Range = MySheet.get_Range("B2", "B800");

Range.Validation.Delete();

Range.NumberFormat = "Text";
//Range.Cells.Value = lstsubject[0].TopicName.ToString();
Range.Cells.Value = "Text";

Range.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop
, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween
, Formula1: "=Topics"
);

Range.Validation.InCellDropdown = true;
Range.Validation.IgnoreBlank = true;



oRange = MySheet.get_Range("c2","c3");

oRange.Validation.Delete();

oRange.NumberFormat = "Text";



oRange.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop
, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween
, Formula1: "=INDIRECT(B2)"
);

oRange.Validation.InCellDropdown = true;
oRange.Validation.IgnoreBlank = true;

IDSheet.Protect("123#");
//Range.Validation.InCellDropdown = true;
//Range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 217, 217, 0));



app.Visible = true;
}

Answer

You need to put double quotes around your cell reference inside the formula: vehicle_makes_dropdown.Formula = "=indirect(""A2"")";

Comments