How to get CustomDocumentProperties using Excel Interop (VB.net)?

Question

I have an Excel document that is created using Excel Interop written in VB.net. Adapting information from this post, I am able to successfully create a workbook and write its CustomDocumentProperties using:

Dim objNewApp As Excel.Application
Dim objNewBook As Excel._Workbook
Dim objNewBooks As Excel.Workbooks

objNewApp = New Excel.Application With {
    .DisplayAlerts = True,
    .Visible = True,
    .UserControl = True,
    .ScreenUpdating = True
    }

objNewBooks = objNewApp.Workbooks
objNewBook = objNewBooks.Add

Dim properties As Object = objNewBook.CustomDocumentProperties
Dim propertiesType As Type = properties.[GetType]()
Dim documentClient As Object() = {"Client", False, Core.MsoDocProperties.msoPropertyTypeString, "In-Progress"}
propertiesType.InvokeMember("Add", BindingFlags.InvokeMethod, Nothing, properties, documentClient)

However, I have not been able to successfully read this property after it is set. After doing a thorough search, most posts on the topic here on SO, as well as MSDN, suggest reading this missing MSDN article to learn more about how to do this, noting how goofy it is to do in Interop in general. However, I am using Interop for the rest of my program, so I would like to find an Interop-specific solution (instead of VSTO).

Adapting from this post, I believe my current code is on the right track:

Dim ReadClient As String = "Client"
Dim ObjReadClient = propertiesType.InvokeMember("Item", BindingFlags.GetProperty, Nothing, properties, New Object() {ReadClient})
Dim TypeReadClient As Type = ObjReadClient.GetType
Dim ClientString As String

ClientString = TypeReadClient.InvokeMember("Value", BindingFlags.GetProperty, Nothing, properties, New Object() {})

However, when I run this I receive an System.Runtime.InteropServices.COMException:

"Unknown name. (Exception from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))"

Doing more research, this appears to be due to the "Value" piece on the last line of code. I was unable to figure out where to go from there.

Does anyone have any idea as to this last piece of the puzzle? I'd be happy to clarify anything if needed!

score 0 · Accepted Answer · answered Mar 29 '19 at 16:16

Here is the solution for my particular problem:

Dim ReadClientIndex As String = "Client"
Dim ReadClientValue As String
Dim ObjReadClient As Object = propertiesType.InvokeMember("Item", BindingFlags.GetProperty, Nothing, properties, New Object() {ReadClientIndex})
Dim TypeReadClient As Type = ObjReadClient.GetType()
ReadClientValue = TypeReadClient.InvokeMember("Value", BindingFlags.GetProperty, Nothing, ObjReadClient, New Object() {})

Apart from cleaning up the code, the issue was that I should have referred to "ObjReadClient" as the argument in the last line - not the "properties" variable set earlier.

However, since there is a notable lack of documentation on this topic in VB.net or C#, here are some resources that might help future users:

Link 1: Provided code originally in the missing MSKB article (in C#, copied again below for preservation.):

Word.Application oWord;
   Word._Document oDoc;
   object oMissing = Missing.Value;
   object oDocBuiltInProps;
   object oDocCustomProps;

   //Create an instance of Microsoft Word and make it visible.
   oWord = new Word.Application();
   oWord.Visible = true;

   //Create a new Document and get the BuiltInDocumentProperties collection.
   oDoc = oWord.Documents.Add(ref oMissing, ref oMissing, ref oMissing, 
                              ref oMissing);
   oDocBuiltInProps = oDoc.BuiltInDocumentProperties;
   Type typeDocBuiltInProps = oDocBuiltInProps.GetType();

   //Get the Author property and display it.
   string strIndex = "Author";
   string strValue;
   object oDocAuthorProp = typeDocBuiltInProps.InvokeMember("Item", 
                              BindingFlags.Default | 
                              BindingFlags.GetProperty, 
                              null,oDocBuiltInProps, 
                              new object[] {strIndex} );
   Type typeDocAuthorProp = oDocAuthorProp.GetType();
   strValue = typeDocAuthorProp.InvokeMember("Value", 
                              BindingFlags.Default |
                              BindingFlags.GetProperty,
                              null,oDocAuthorProp,
                              new object[] {} ).ToString();
   MessageBox.Show( "The Author is: " + strValue,"Author" );

   //Set the Subject property.
   strIndex = "Subject";
   strValue = "The Subject";
   typeDocAuthorProp.InvokeMember("Item", 
                              BindingFlags.Default | 
                              BindingFlags.SetProperty, 
                              null,oDocBuiltInProps, 
                              new object[] {strIndex,strValue} );

   //Add a property/value pair to the CustomDocumentProperties collection.
   oDocCustomProps = oDoc.CustomDocumentProperties;
   Type typeDocCustomProps = oDocCustomProps.GetType();

   strIndex = "Knowledge Base Article";
   strValue = "Q303296";
   object[] oArgs = {strIndex,false,
                     MsoDocProperties.msoPropertyTypeString,
                     strValue};

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

   MessageBox.Show("Select \"Properties\" from the File menu "
        + "to view the changes.\nSelect the Summary tab to view "
        + "the Subject property and the Custom tab to view the Knowledge"   
        + "Base Article property.", "Check File Properties",
        MessageBoxButtons.OK,MessageBoxIcon.Information);

Link 2: Notes that it is easier to accomplish in VB.net, but that C# will soon support late-binding using "Dynamic" (written 10 years ago). I found another post somewhere that explained the importance of "Dynamic" as an answer in C#, but was unable to find it again to link to.

Link 3: This information is specific to Excel, but I believe it may help someone looking for it specifically.

Link 4: This gives an example mistaking VTSO and Interop, which may help users differentiate between the two.

score 0 · Answer 2 · answered Jan 11 '20 at 15:19

0

To get custom properties using c# is bit a different approach. Pls check these 2.

Not copying whole code for dedup.

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

How to get CustomDocumentProperties using Excel Interop?

answered Jan 11 '20 at 15:19

Hesoti

87
1
8

How to get CustomDocumentProperties using Excel Interop (VB.net)?

2 Answers2