Accessing Excel Custom Document Properties programatically

Question

I'm trying to add custom properties to a workbook I have created programatically. 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)

Answer 1

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. :-)

Answer 2

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