insert data object array which contains Excel formula's into existing Excel sheet on a given range

Question

I have a dataobject array object[,] coming from an SQL database table loaded through SqlDataAdapter and it contains Excel formulas.

I want to paste the object into a certain range in an already opened Excel file.

I should add that everything works fine when the datatable holds strings like "this is a test" and not Excel formulas like "=SUM(A1:A5)"

But I keep running into exceptions that make no sense to me. Please advice!

Here's what I've got. What am I overlooking?

    private void InsertBridgeCalcBlock()
    {
        Excel.Application xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

        try
        {
            xlActiveCell = xlApp.ActiveCell;
            DataTable dt = new DataTable();

            try
            {
                using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM " + sDBBridgeCalcTable, conn))
                {
                    da.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Message:" + Environment.NewLine + ex);
            }

            object[,] BridgeCalcTable = new object[dt.Rows.Count + 1,dt.Columns.Count];
            for (var i = 0; i < dt.Rows.Count; i++)
                for (var j = 1; j < dt.Columns.Count; j++)
                {
                    BridgeCalcTable[i, j-1] = dt.Rows[i][j];
                }

            Excel.Range insertBridgeCalcTableRange = xlApp.Range[xlApp.ActiveSheet.Cells[xlActiveCell.Row-2, 11], xlApp.ActiveSheet.Cells[xlActiveCell.Row-2 + dt.Rows.Count - 1, 11 + dt.Columns.Count]]; // set insertrange
            xlApp.ActiveSheet.EnableCalculation = true;
            insertBridgeCalcTableRange.Value = BridgeCalcTable; // fill range with data

        }
        catch (Exception ex)
        {
            MessageBox.Show("Error Message:" + Environment.NewLine + ex);
        }
    }

The exception reads:

    System.Runtime.InteropServices.COMException (0x800A03EC): 
    Exception from HRESULT: 0x800A03EC at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWarpperTypes, MessageData& msgData)
    at Microsoft.Office.Interop.Excel.Rage.ser_Value(obejct value) at LookApp2016.Form1.InsertBridgeCalcBlock() in ~~myfilelocation~~ line 2886

Answer 1

Although I still want to know why Interop is throwing an exception when using Excel formulas in the datatable,

I've found a possible answer through inputting with Excel.Range.CopyFromRecordSet. Like so:

        ADODB.Recordset BridgeCalcRecordset = ConvertToRecordset(dt);
        insertBridgeCalcTableRange.CopyFromRecordset(BridgeCalcRecordset);

However, now my formulas are not automattically recognised as formulas. I have to edit and exit each cell before calculation starts and the formula changes into the result...

I solved this using:

     insertBridgeCalcTableRange.Formula = insertBridgeCalcTableRange.Value;

My code is the same but I'm using this code to convert my DataSet into a ADODB RecordSet (they say it's faster that way too): credits to Web Star at http://www.nullskull.com/q/10057748/hi-all.aspx

    static public ADODB.Recordset ConvertToRecordset(DataTable inTable)
    {
        ADODB.Recordset result = new ADODB.Recordset();
        result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

        ADODB.Fields resultFields = result.Fields;
        System.Data.DataColumnCollection inColumns = inTable.Columns;

        foreach (DataColumn inColumn in inColumns)
        {
            resultFields.Append(inColumn.ColumnName
                , TranslateType(inColumn.DataType)
                , inColumn.MaxLength
                , inColumn.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable :
                                         ADODB.FieldAttributeEnum.adFldUnspecified
                , null);
        }

        result.Open(System.Reflection.Missing.Value
                , System.Reflection.Missing.Value
                , ADODB.CursorTypeEnum.adOpenStatic
                , ADODB.LockTypeEnum.adLockOptimistic, 0);

        foreach (DataRow dr in inTable.Rows)
        {
            result.AddNew(System.Reflection.Missing.Value,
                          System.Reflection.Missing.Value);

            for (int columnIndex = 0; columnIndex < inColumns.Count; columnIndex++)
            {
                resultFields[columnIndex].Value = dr[columnIndex];
            }
        }

        return result;
    }

    static ADODB.DataTypeEnum TranslateType(Type columnType)
    {
        switch (columnType.UnderlyingSystemType.ToString())
        {
            case "System.Boolean":
                return ADODB.DataTypeEnum.adBoolean;

            case "System.Byte":
                return ADODB.DataTypeEnum.adUnsignedTinyInt;

            case "System.Char":
                return ADODB.DataTypeEnum.adChar;

            case "System.DateTime":
                return ADODB.DataTypeEnum.adDate;

            case "System.Decimal":
                return ADODB.DataTypeEnum.adCurrency;

            case "System.Double":
                return ADODB.DataTypeEnum.adDouble;

            case "System.Int16":
                return ADODB.DataTypeEnum.adSmallInt;

            case "System.Int32":
                return ADODB.DataTypeEnum.adInteger;

            case "System.Int64":
                return ADODB.DataTypeEnum.adBigInt;

            case "System.SByte":
                return ADODB.DataTypeEnum.adTinyInt;

            case "System.Single":
                return ADODB.DataTypeEnum.adSingle;

            case "System.UInt16":
                return ADODB.DataTypeEnum.adUnsignedSmallInt;

            case "System.UInt32":
                return ADODB.DataTypeEnum.adUnsignedInt;

            case "System.UInt64":
                return ADODB.DataTypeEnum.adUnsignedBigInt;

            case "System.String":
            default:
                return ADODB.DataTypeEnum.adVarChar;
        }
    }