Accessing Excel Custom Document Properties programmatically

Question

I'm trying to add custom properties to a workbook I have created programmatically. I have a method in place for getting and setting properties, but the problem is the workbook is returning null for the CustomDocumentProperties property. I cannot figure out how to initialize this property so that I can add and retrieve properties from the workbook. Microsoft.Office.Core.DocumentProperties is an interface, so I cant go and do the following

if(workbook.CustomDocumentProperties == null)
    workbook.CustomDocumentProperties = new DocumentProperties;

Here is the code I have to get and set the properties:

     private object GetDocumentProperty(string propertyName, MsoDocProperties type)
    {
        object returnVal = null;

        Microsoft.Office.Core.DocumentProperties properties;
        properties = (Microsoft.Office.Core.DocumentProperties)workBk.CustomDocumentProperties;

        foreach (Microsoft.Office.Core.DocumentProperty property in properties)
        {
            if (property.Name == propertyName && property.Type == type)
            {
                returnVal = property.Value;
            }
            DisposeComObject(property);
        }

        DisposeComObject(properties);

        return returnVal;
    }

    protected void SetDocumentProperty(string propertyName, string propertyValue)
    {
        DocumentProperties properties;
        properties = workBk.CustomDocumentProperties as DocumentProperties;

        bool propertyExists = false;
        foreach (DocumentProperty prop in properties)
        {
            if (prop.Name == propertyName)
            {
                prop.Value = propertyValue;
                propertyExists = true;
            }
            DisposeComObject(prop);

            if(propertyExists) break;
        }

        if (!propertyExists)
        {
            properties.Add(propertyName, false, MsoDocProperties.msoPropertyTypeString, propertyValue, Type.Missing);
        }

        DisposeComObject(propertyExists);

    }

The line properties = workBk.CustomDocumentProperties as DocumentProperties; always set properties to null.

This is using Microsoft.Office.Core v12.0.0.0 and Microsoft.Office.Interop.Excell v12.0.0.0 (Office 2007)

score 13 · Answer 1 · answered Jul 05 '12 at 21:27

If you are targetting .NET 4.0, you can use the dynamic key word for late binding

 Document doc = GetActiveDocument();
 if ( doc != null )
 {
     dynamic properties = doc.CustomDocumentProperties;
     foreach (dynamic p in properties)
     {
         Console.WriteLine( p.Name + " " + p.Value);
     }
 }

Gary McGill · Accepted Answer · 2017-03-08T17:08:03.923

11

I looked at my own code and can see that I access the properties using late binding. I can't remember why, but I'll post some code in case it helps.

object properties = workBk.GetType().InvokeMember("CustomDocumentProperties", BindingFlags.Default | BindingFlags.GetProperty, null, workBk, null);

object property = properties.GetType().InvokeMember("Item", BindingFlags.Default | BindingFlags.GetProperty, null, properties, new object[] { propertyIndex });

object propertyValue = property.GetType().InvokeMember("Value", BindingFlags.Default | BindingFlags.GetProperty, null, propertyWrapper.Object, null);

EDIT: ah, now I remember why. :-)

EDIT 2: Jimbojones' answer - to use the dynamic keyword - is a better solution (if you value ease-of-use over the performance overhead of using dynamic).

edited Mar 08 '17 at 17:08

answered Jul 16 '09 at 14:10

Gary McGill

26,400
25
118
202

Exactly the link I found, and I posted my code while you were posting this. +1 and Accepted for you :-) – Dan McClain Jul 16 '09 at 14:18
@CindyMeister Link is no longer working, and I was unable to find the kb article. – Kolky Apr 04 '18 at 13:58
@Kolky I can't turn it up, either. I'm asking around... FWIW a hint is in the Answer about using `dynamic` to enable late-binding. These are "late bound" into the Office applications - not really part of their native object models, even though VB developers can access them as if they were. But C# (before `dynamic`) can't do this. – Cindy Meister Apr 04 '18 at 15:49
@CindyMeister Strange that the kb article is gone. Even stranger, why with both the same version of Office and Office.Interop references (15.0.0) I can't just cast it to the right type. `Dynamic` works, it's just not very pretty. – Kolky Apr 04 '18 at 22:00

score 8 · Answer 3 · answered Jul 16 '09 at 14:17

I found the solution here.

Here is the code I ended up with:

    public void SetDocumentProperty(string propertyName, string propertyValue)
    {
        object oDocCustomProps = workBk.CustomDocumentProperties;
        Type typeDocCustomProps = oDocCustomProps.GetType();

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

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

    }

    private object GetDocumentProperty(string propertyName, MsoDocProperties type)
    {
        object returnVal = null;

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


        object returned = typeDocCustomProps.InvokeMember("Item", 
                                    BindingFlags.Default |
                                   BindingFlags.GetProperty, null,
                                   oDocCustomProps, new object[] { propertyName });

        Type typeDocAuthorProp = returned.GetType();
        returnVal = typeDocAuthorProp.InvokeMember("Value",
                                   BindingFlags.Default |
                                   BindingFlags.GetProperty,
                                   null, returned,
                                   new object[] { }).ToString();

        return returnVal;
    }

Some exception handling is necessary to hand if the property doesnt exist when retrieved

cori · Answer 4 · 2011-10-14T12:43:49.150

Late answer to this question, but I worked out a simpler method for adding custom DocumentProperties that might be of use to someone in the future.

My problem was that calling the Add() method with the System type supplied by System.String.GetType() triggered a COMException: Type mismatch. Referring to the link in the previous answers it's clear that this method expects an Office-specific type, so the code that ended up working for me was:

var custProps = (Office.DocumentProperties)this.CustomDocumentProperties;
custProps.Add( "AProperty", false, MsoDocProperties.msoPropertyTypeString, "AStringProperty" );

Because it's a CustomDocumentProperty Office will add the custom property without difficulty, but if you need to check for existence or validate the value when the CustomDocumentProperty might not exist you'll have to catch a System.ArgumentException.

EDIT

As pointed out in Oliver Bock's comment, this is an Office 2007 and up only solution, as far as I know.

I find (with Excel 2000) that I cannot cast workbook.CustomDocumentProperties to Office.DocumentProperties. The reflections code in other answers does work, however. — Oliver Bock, Oct 14 '11 at 05:00
Ah, yes - I should have noted (and have done so now) that I believe this is a Office 2007 and up only feature (although it might work in 2003 - I no longer have access to that in order to test). In my defense, that's what the original poster was using. — cori, Oct 14 '11 at 12:44

Accessing Excel Custom Document Properties programmatically

4 Answers4

Linked