How to use C# Interop EXCEL to create "Measure"

Question

Please Help! // ERROR System.Runtime.InteropServices.COMException: '0x800A03EC'

Excel.PivotField newMeasure = calculatedFields.Add("NewMeasure", formula, true);

AddMeasure(task.attachment.FullName, 
    WorkSheetIndex: GetWorksheetIndex(task, "Promo Voice total"), 
    pivotTableName: "pt1", 
    MeasuresName: "myesures", 
    formula: "=A1+B1");

public static void AddMeasure(string file, int WorkSheetIndex, string pivotTableName, string MeasuresName, string formula)
{
    Excel.Application excelApp = new Excel.Application();

    try
    {
        Excel.Workbook workbook = excelApp.Workbooks.Open(file);
        Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[WorkSheetIndex];

        Excel.PivotTable pivotTable = worksheet.PivotTables(pivotTableName) as Excel.PivotTable;

        if (pivotTable != null)
        {
            Excel.CalculatedFields calculatedFields = pivotTable.CalculatedFields();
            if (calculatedFields != null)
            {
                Excel.PivotField newMeasure = calculatedFields.Add("NewMeasure", formula, true);**// HERE ERROR `System.Runtime.InteropServices.COMException: '0x800A03EC'`**

                ((Excel.PivotField)pivotTable.PivotFields($"{MeasuresName}")).Orientation = Excel.XlPivotFieldOrientation.xlDataField;

                if (newMeasure != null)
                {
                    pivotTable.AddDataField(newMeasure);
                    Console.WriteLine($"Measure '{MeasuresName}' added successfully.");
                }}}
        workbook.Save();
    }
    catch (Exception ex)
    {
        Console.WriteLine($"An error occurred: {ex.Message}");
    }
    finally
    {
        excelApp.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
    }
}

// ERROR System.Runtime.InteropServices.COMException: '0x800A03EC'

Excel.PivotField newMeasure = calculatedFields.Add("NewMeasure", formula, true);

Answer 1

---

VB.NET

I quickly wrote this code in VB.net. It works as expected. Later in the day if I get the time, I will update the post with the C# code if you are still stuck...

Before:

Code:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim Mdl As Excel.Model
        Dim tbl As Excel.ModelTable

        With xlApp
            .Visible = True

            '~~> Open workbook
            xlWb = .Workbooks.Open("C:\Users\routs\Downloads\Test.xlsm")

            Mdl = xlWb.Model
            tbl = Mdl.ModelTables("Range")

            '~~> Delete the measure if it already exists
            Try
                Mdl.ModelMeasures("NewMeasure").Delete
            Catch ex As Exception
            End Try

            Try
                Mdl.ModelMeasures.Add("NewMeasure", tbl, "1+2", Mdl.ModelFormatDecimalNumber(False, 2))
            Catch ex As Exception
                MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End With

        '~~> Save and Close workbook and quit Excel
        xlWb.Close(True)
        xlApp.Quit()

        '~~> Flush the toilet
        Kawoosh(tbl)
        Kawoosh(xlWb)
        Kawoosh(Mdl)
        Kawoosh(xlApp)
    End Sub

    Private Sub Kawoosh(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Output

---

C#

Option Strict is switched off by default in VB.Net and hence it allows implicit data type conversion. If you do a straight conversion of the code that I gave above to C# it will error out on the line as shown below. I believe this is the error that you were getting...

In VB.net when you right click on your project and select "Properties", you get the Compile tab (as shown below) where you can tweak the Option Strict property.

This tab is missing in C# (as shown below).

This setting does not exist in C#, because like I mentioned above implicit data type conversion is not allowed in C#.

So how do we tackle this? We use the dynamic keyword. This keyword brings Option Strict Off equivalent functionality to C#.

Try this code. I have tested it. It works.

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWb;
            // Excel.Model Mdl;
            Excel.ModelTable tbl;

            {
                xlApp.Visible = true;

                // ~~> Open workbook
                xlWb = xlApp.Workbooks.Open(@"C:\Users\routs\Downloads\Test.xlsm");

                dynamic Mdl = xlWb.Model;

                tbl = Mdl.ModelTables["Range"];

                try
                {
                    Mdl.ModelMeasures["NewMeasure"].Delete();
                }
                catch (Exception ex)
                {
                }

                try
                {
                    Mdl.ModelMeasures.Add("NewMeasure", tbl, "1+2", Mdl.ModelFormatDecimalNumber(false, 2));
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

            // ~~> Close workbook and quit Excel
            xlWb.Close(true);
            xlApp.Quit();

            // ~~> Flush the toilet
            Kawoosh(tbl);
            Kawoosh(xlWb);
            Kawoosh(xlApp);
        }
        private void Kawoosh(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}