I am relatively new to c#. My code continually fails to insert a value to a cell. I have been on this for multiple days now. I have tried almost everything (as you can see in the code) but keep getting unknown errors.

Usually this error:

System.Runtime.InteropServices.COMException (0x800A03EC): 0x800A03EC
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Object[] aArgs, Boolean[] aArgsIsByRef, Int32[] aArgsWrapperTypes, Type[] aArgsTypes, Type retType)
   at Microsoft.Office.Interop.Excel.Range.set__Default(Object RowIndex, Object ColumnIndex, Object )

I am using interop excel and trying to call functions in excel using excelDNA. Basically, I am trying to get values within a range (["A1", "A100"]) from a sheet called prefill_sheet. Then loop through each cell in the range, get it's value and pass it to openai api for processing. This part has worked so far.

My only issue is when trying to add the values to another sheet called summarySheet. I have tried adding the value cell by cell inside a for loop and it still gives me the same error. I have tried using a range to set the value. Same error. When I use range.set_Value(), I get a type mismatch error.

CODE:

public static class AIPrefillSummarization{

[ExcelFunction(Description = "Summarize Prefill", Name = "SummarizePrefill")]
public static string Summarize_Prefill()
{
    try
    {
        Application excelApp = Marshal2.GetActiveObject("Excel.Application") as Application;

        Worksheet? prefill_sheet = excelApp.Sheets["Prefill"] as Worksheet;
        Worksheet? summarySheet = excelApp.Sheets["Prefill Summary"] as Worksheet;
        Worksheet? configSheet = excelApp.Sheets["Config"] as Worksheet;


        if (prefill_sheet != null && configSheet != null && summarySheet != null)
        {
            String? apiKey = configSheet.Range["A1"].Value as String;
            prefill_sheet.Visible = XlSheetVisibility.xlSheetVisible;
            prefill_sheet.Unprotect();

            Excel_Range prefillRange = prefill_sheet.Range["A1", "A100"];

            if (apiKey == null || apiKey.Equals(""))
            {
                return "API Key not found in Config sheet A1!";
            }

            List<string> summaries = new List<string>();
            for (int i = 0; i < prefillRange.Rows.Count; i++)
            {
                Excel_Range cell = (Excel_Range) prefillRange.Cells[i + 1, 1];
                if (cell == null)
                {
                    continue;
                }
                object cellValue = cell.Value;
                string? value = cellValue?.ToString();
                if (value == null || value == "")
                {
                    summaries.Add("HELLO ALEX...");
                    continue;
                }
                else
                {
                    //Console.WriteLine($"cell_value: {value}");
                    //summaries.Add(SendToChatGPT(value, apiKey));
                    summaries.Add("HELLO ALEX...");
                }
            }

            Excel_Range allCells = summarySheet.Cells;
            allCells?.ClearContents();

            // Assuming 'summarySheet' is an Excel worksheet object and 'summaries' is a List<string>
            Excel_Range range = summarySheet.Range["A1"];
            var summariesArray = summaries.ToArray();

            range.ClearContents();
            summarySheet.Activate();
            

            // Create a two-dimensional array (1 column, N rows)
            int startRow = 1;
            object[,] outputArray = new object[summariesArray.Length, 1];
            for (int i = 0; i < summariesArray.Length; i++)
            {
                //Excel_Range cell = (Excel_Range)summarySheet.Rows.Cells[startRow + i, 1];
                try
                {
                    outputArray[i, 0] = summariesArray[i];
                    //cell.Value = "Hello";
                }
                catch (Exception ex)
                {
                    return $"Error writing to cell {startRow + i}, {1}: {ex}: range.Address: {range.Address}";
                }
            }

            // Resize the range to match the number of items in summaries
            range = range.Resize[summariesArray.Length, 1];
            StringBuilder debugOutput = new StringBuilder();
            for (int i = 0; i < summariesArray.Length; i++)
            {
                debugOutput.AppendLine($"Row {i + 1}: {outputArray[i, 0]}");
            }

            //return debugOutput.ToString();

            // Assign the values to the Excel range
            range.set_Value(outputArray);
            //range.Value2 = outputArray;

        }
        return "Prefill summarized!";
    }
    catch (Exception ex)
    {
        return ex.ToString();
    }
}

I am relatively new to c#. My code continually fails to insert a value to a cell. I have been on this for multiple days now. I have tried almost everything (as you can see in the code) but keep getting unknown errors.

Usually this error:

System.Runtime.InteropServices.COMException (0x800A03EC): 0x800A03EC
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Object[] aArgs, Boolean[] aArgsIsByRef, Int32[] aArgsWrapperTypes, Type[] aArgsTypes, Type retType)
   at Microsoft.Office.Interop.Excel.Range.set__Default(Object RowIndex, Object ColumnIndex, Object )

I am using interop excel and trying to call functions in excel using excelDNA. Basically, I am trying to get values within a range (["A1", "A100"]) from a sheet called prefill_sheet. Then loop through each cell in the range, get it's value and pass it to openai api for processing. This part has worked so far.

My only issue is when trying to add the values to another sheet called summarySheet. I have tried adding the value cell by cell inside a for loop and it still gives me the same error. I have tried using a range to set the value. Same error. When I use range.set_Value(), I get a type mismatch error.

CODE:

public static class AIPrefillSummarization{

[ExcelFunction(Description = "Summarize Prefill", Name = "SummarizePrefill")]
public static string Summarize_Prefill()
{
    try
    {
        Application excelApp = Marshal2.GetActiveObject("Excel.Application") as Application;

        Worksheet? prefill_sheet = excelApp.Sheets["Prefill"] as Worksheet;
        Worksheet? summarySheet = excelApp.Sheets["Prefill Summary"] as Worksheet;
        Worksheet? configSheet = excelApp.Sheets["Config"] as Worksheet;


        if (prefill_sheet != null && configSheet != null && summarySheet != null)
        {
            String? apiKey = configSheet.Range["A1"].Value as String;
            prefill_sheet.Visible = XlSheetVisibility.xlSheetVisible;
            prefill_sheet.Unprotect();

            Excel_Range prefillRange = prefill_sheet.Range["A1", "A100"];

            if (apiKey == null || apiKey.Equals(""))
            {
                return "API Key not found in Config sheet A1!";
            }

            List<string> summaries = new List<string>();
            for (int i = 0; i < prefillRange.Rows.Count; i++)
            {
                Excel_Range cell = (Excel_Range) prefillRange.Cells[i + 1, 1];
                if (cell == null)
                {
                    continue;
                }
                object cellValue = cell.Value;
                string? value = cellValue?.ToString();
                if (value == null || value == "")
                {
                    summaries.Add("HELLO ALEX...");
                    continue;
                }
                else
                {
                    //Console.WriteLine($"cell_value: {value}");
                    //summaries.Add(SendToChatGPT(value, apiKey));
                    summaries.Add("HELLO ALEX...");
                }
            }

            Excel_Range allCells = summarySheet.Cells;
            allCells?.ClearContents();

            // Assuming 'summarySheet' is an Excel worksheet object and 'summaries' is a List<string>
            Excel_Range range = summarySheet.Range["A1"];
            var summariesArray = summaries.ToArray();

            range.ClearContents();
            summarySheet.Activate();
            

            // Create a two-dimensional array (1 column, N rows)
            int startRow = 1;
            object[,] outputArray = new object[summariesArray.Length, 1];
            for (int i = 0; i < summariesArray.Length; i++)
            {
                //Excel_Range cell = (Excel_Range)summarySheet.Rows.Cells[startRow + i, 1];
                try
                {
                    outputArray[i, 0] = summariesArray[i];
                    //cell.Value = "Hello";
                }
                catch (Exception ex)
                {
                    return $"Error writing to cell {startRow + i}, {1}: {ex}: range.Address: {range.Address}";
                }
            }

            // Resize the range to match the number of items in summaries
            range = range.Resize[summariesArray.Length, 1];
            StringBuilder debugOutput = new StringBuilder();
            for (int i = 0; i < summariesArray.Length; i++)
            {
                debugOutput.AppendLine($"Row {i + 1}: {outputArray[i, 0]}");
            }

            //return debugOutput.ToString();

            // Assign the values to the Excel range
            range.set_Value(outputArray);
            //range.Value2 = outputArray;

        }
        return "Prefill summarized!";
    }
    catch (Exception ex)
    {
        return ex.ToString();
    }
}

• c#
• asp
• excel
• interop
• excel-dna

• 1 Interop is notoriously flakey. Unless you need to read values in real-time from a spreadsheet the user needs to have open on the screen, then you would almost certainly be better to use one of the many libraries which reads directly from the Excel file on disk rather than relying on communication with an open instance of the application. – ADyson Commented Feb 3 at 9:52
• How are you calling the function Summarize_Prefill()? Are you calling =Summarize_Prefill() as a formula in a spreadsheet cell? – DS_London Commented Feb 3 at 10:12
• @DS_London Yes. I am using =SummarizePrefill() not =Summarize_Prefill – Alexander Obidiegwu Commented Feb 3 at 10:23
• 1 This is related to this answer: stackoverflow/questions/70740797/…. The designers of Excel do not want spreadsheet functions changing the values of cells elsewhere in a spreadsheet. One reason is that it opens up the possibility of circular references, confusing the calculation tree (and opening up the risk of corrupting the sheet). The function would work if called from a macro. – DS_London Commented Feb 3 at 15:00
• 1 @ADyson Yes, the first method may well allow you circumvent Excel's safety restrictions, as it will likely appear that the code is calling into Excel from the outside. – DS_London Commented Feb 3 at 16:10

1 Answer 1

Looks like you're trying to update cells other than the cell where the function is running. that's not allowed using standard user defined functions as it breaks the execution model.

If you want to run a function in one cell that modifies values/layout of other cells the easiest way is to use the QueueAsMacro() function to run the code on the Macro thread after the UDF completes.