How to get CustomDocumentProperties using Excel Interop?

Question

The below code is used to get the custom document properties for Excel workbook.

var xlApp = Globals.ThisAddIn.Application; // This works in VSTO Excel Add-in
var xlApp = new global::Microsoft.Office.Interop.Excel.Application(); // This doesn't work anywhere
xlApp.Visible = true;
global::Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Open(file, false, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false, Type.Missing, Type.Missing);

global::Microsoft.Office.Core.DocumentProperties properties = workbook.CustomDocumentProperties; // Exception occurs here
global::Microsoft.Office.Core.DocumentProperty property = properties["propertyname"];

The first 2 lines are references to the Excel Application. One obtain the reference from VSTO add-in internals, the other is a regular new Application().

When using the Application from VSTO internals, the code run fines without any problems. But when using new Application(), the workbook.CustomDocumentProperties line throws InvalidCastException:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Core.DocumentProperties'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{2DF8D04D-5BFA-101B-BDE5-00AA0044DE52}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

I am trying to make it to work on a C# winforms project without VSTO. A lot of examples and tutorials use new Application() for Excel interop, but I noticed that Microsoft.Office.Interop.Excel.Application is an interface, so using new on interface is actually strange to me. How can I create a proper Application that can get the CustomDocumentProperties?

Reference Assemblies I am using:

• Microsoft.Office.Interop.Excel v2.0.50727 Version 14.0.0.0
• Microsoft.CSharp v4.0.30319 Version 4.0.0.0

Answer 1

I noticed that Microsoft.Office.Interop.Excel.Application is an interface, so using new on interface is actually strange to me.

That is strange indeed, but by design. The Excel.Application interface is decorated with the CoClass attribute telling the actual class to instantiate on 'instantiating' the interface. More about it here.

But when using new Application(), the workbook.CustomDocumentProperties line throws InvalidCastException:

Strange indeed again. I have experienced some issues myself using document properties. It seems that the actual class returned differs from the spec, so I moved to use dynamic in order to prevent type casting issues.

So instead of this:

Microsoft.Office.Core.DocumentProperties properties = workbook.CustomDocumentProperties;

Use:

dynamic properties = workbook.CustomDocumentProperties;

Answer 2

How can I create a proper Application that can get the CustomDocumentProperties?

There is no need to create a new Excel Application instance if you develop an add-in. You should use the Application property provided by the VSTO runtime:

var xlApp = Globals.ThisAddIn.Application; // This works in VSTO Excel Add-in

But if you develop a standalone application which automates Excel, in that case you need to create a new Application instance by using the new operator:

var xlApp = new global::Microsoft.Office.Interop.Excel.Application(); 

Use the late binding technology (Type.InvokeMember) for getting or setting document property as the How To Use Automation to Get and to Set Office Document Properties with Visual C# .NET article suggests.

Answer 3

I've had a same problem. Today it has been resolved. There is a different approach to derive the results. The question and its' answer is at

How can I read excel custom document property using c# excel interop

Here is my implementation.

public string CheckDocProp(string propName, object props)
        {
            Excel.Workbook workBk = Globals.ThisAddIn.Application.ActiveWorkbook;

            object customProperties = workBk.CustomDocumentProperties;
            Type docPropsType = customProperties.GetType();
            object nrProps;
            object itemProp = null;
            object oPropName;
            object oPropVal = null;

            nrProps = docPropsType.InvokeMember("Count",
                BindingFlags.GetProperty | BindingFlags.Default,
                null, props, new object[] { });
            int iProps = (int)nrProps;

            for (int counter = 1; counter <= ((int)nrProps); counter++)
            {
                itemProp = docPropsType.InvokeMember("Item",
                    BindingFlags.GetProperty | BindingFlags.Default,
                    null, props, new object[] { counter });

                oPropName = docPropsType.InvokeMember("Name",
                    BindingFlags.GetProperty | BindingFlags.Default,
                    null, itemProp, new object[] { });

                if (propName == oPropName.ToString())
                {
                    oPropVal = docPropsType.InvokeMember("Value",
                        BindingFlags.GetProperty | BindingFlags.Default,
                        null, itemProp, new object[] { });
                    return oPropVal.ToString();
                    break;
                }
                else
                {
                    return "Not Found.";
                }
            }
            return "Not Found.";
        }

Usage:

object docProps = wb.CustomDocumentProperties;
            string prop1 = ExistsDocProp("<CustomProperty>", docProps);