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

Question

I'm trying to check if a custom document property has been set for an excel file or not. And if set then read the value.

Here is the code I'm using but so far no luck. It doesn't get into the foreach loop and comes out.

var propval = ReadDocumentProperty("TestProp");

private string ReadDocumentProperty(string propertyName)
{
    Office.DocumentProperties properties;
    Excel.Workbook Wb = Globals.ThisAddIn.Application.ActiveWorkbook;
    properties = (Office.DocumentProperties)Wb.CustomDocumentProperties;

    foreach (Office.DocumentProperty prop in properties)
    {
        if (prop.Name == propertyName)
        {
            return prop.Value.ToString();
        }
    }
    return null;
}

Update1:

I found this code for setting the custom property.

Excel.Workbook workBk = Globals.ThisAddIn.Application.ActiveWorkbook;

            object oDocCustomProps = workBk.CustomDocumentProperties;
            Type typeDocCustomProps = oDocCustomProps.GetType();

            object[] oArgs = {propertyName,false,
         Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString,
         propertyValue};

            typeDocCustomProps.InvokeMember("Add", BindingFlags.Default |
                                       BindingFlags.InvokeMethod, null,
                                       oDocCustomProps, oArgs);

This works fine to set the custom property. I have no clue how to modify it to read the property value.

If it doesn't get into the loop then there are no custom properties. The normal case. — Hans Passant, Jan 10 '20 at 14:54
@Hesoti code worked when I ran it, maybe it's an issue with how you're testing it. Are you saving the resulting dirty workbook after adding the properties? You should be able to simplify the add as well - here's example of both that worked: https://learn.microsoft.com/en-us/visualstudio/vsto/how-to-create-and-modify-custom-document-properties?view=vs-2019 — MikeJ, Jan 10 '20 at 18:14
The first set of code is specific to VSTO and will only work if one is using the "Tools". @MikeJ If you read the question thoroughly you'll see that the second set of code does work, it just doesn't do what's required. That code *adds* document properties, but the OP wants to *read* them. It's not an attempt... — Cindy Meister, Jan 10 '20 at 18:37
@CindyMeister my comment had to do with the snippet that reads the properties - for me it worked as he posted it. And reading the question thoroughly it's the first code block that was not working for him - there is no loop in the second one. — MikeJ, Apr 26 '20 at 20:29

score 4 · Accepted Answer · answered Jan 10 '20 at 18:35

The principle is the same. Some research into how to use PInvoke would help when it's required for working with the Office "interop". In order to use it, it's necessary to fully understand the part of the Office object model you need to address: the object, the property or method and exactly what arguments are required as there is no IntelliSense that can help. First testing in the VBA interface can make this easier.

The following code snippet which I have in a test project demonstrates how to address a single Document Property and read, then write its value. Note that the sample code works with BuiltInDocumentProperties. This can be changed to CustomDocumentProperties if that's what is required.

    private void btnUpdateCustomDocProp_Click(object sender, EventArgs e)
    {
        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
        Excel.Application xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.application");
        Excel.Workbook wb = xlApp.ActiveWorkbook;
        object docProps = wb.BuiltinDocumentProperties;

        object prop = ExistsDocProp("Author", docProps);
        if (prop!=null)
        {
            object oProp = prop;
            Type oPropType = oProp.GetType();
            //read current value
            string propValue = oPropType.InvokeMember("Value",
                BindingFlags.GetProperty | BindingFlags.Default,
                null, oProp, new object[] { }).ToString();

            object oPropValue = "new test author";
            //write new value
            oPropType.InvokeMember("Value",
                BindingFlags.SetProperty | BindingFlags.Default,
                null, oProp, new object[] {oPropValue});

            MessageBox.Show(propValue + ", " + oPropValue.ToString());         
        }
    }

    private object ExistsDocProp(string propName, object props)
    {
        Office.DocumentProperty customDocProp = null;
        Type docPropsType = props.GetType();
        object nrProps;
        object itemProp = null;
        object oPropName;

        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())
            {
                break;
            }
        }
        return itemProp; 
    }

This resolves my problem. Thanks a lot. to get custom doc properties I've changed it a bit a suggested by you. `object customProperties = workBk.CustomDocumentProperties; Type docPropsType = customProperties.GetType();` — Hesoti, Jan 11 '20 at 08:48
@CindyMeister I'm curious why you would need to use the system.runtime methods instead of simply using the interfaces directly? — MikeJ, Apr 26 '20 at 20:36
@MikeJ I'm not sure what you mean with system.runtime methods vs. Interfaces? You mean, why is PInvoke necessary? — Cindy Meister, Apr 27 '20 at 19:32
@CindyMeister what you're doing is not PInvoke. You're using the .net runtime to bind to C# interfaces. I'm wondering why that is necessary. — MikeJ, May 01 '20 at 17:20
@MikeJ `DocumentProperties` are actually an *Office*, not a Word object. Word bases its `BuiltinDocumentProperties` and `CustomDocumentProperties` on that in its object model, and offers these as if they were "native" to Word. This works fine in VBA. But C#, being stricter, chokes on it and can only access such things via "late-binding", rather than through the Word PIAs. Same goes for anything using the `WordBasic` "namespace" (which at least gives you a clue since there's no Intellisense even in VBA for that). I've looked for an article in the MS Docs that uses "better" terminology, but... — Cindy Meister, May 08 '20 at 15:17
@CindyMeister thanks for the explanation. I'm not sure I'm completely following it. I was able to set/read the properties without using late binding. Perhaps he simply didn't have the correct PIAs in his project. The documentproperties interfaces are in the office core PIA. — MikeJ, Jun 05 '20 at 18:08
Just to clarify. I have tried using this with the Microsoft Excel interop from Nuget Manager and not the COM-object (when you have Excel installed). If trying to use the interop in nuget I could not get this to work. Removing Nuget package and modifying the code to use the MS-com object makes it work. It requires Excel to be installed though.... — Magnus_G, Oct 03 '21 at 15:00

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

1 Answers1

Linked