How to get Excel Workbook CustomDocumentProperties?

[zou]

Anyone knows what is the datatype returned from this property node?

[aputman]

It should return a collection of document properties, which can be indexed.  Not sure why you are seeing a variant.  Maybe there is some way to cast it back to what you expect, using the Variant to Data VI.  However, I haven't been able to get anything to work yet. 

[zou]

Thanks for reply.

It probably is a version compatible issue.

What version are you using?

I'm using Excel 2007 on Windows 7 Pro. 64 bit.

I've tried LabVIEW 2011-2017.

I can cast it back to a ref. without getting any error,

but can't use the ref. for any properties or invoke node.

Error -2147352573.

I'm not using RGT, just ActiveX.

[aputman]

I get the same error

Excel 2010 on Windows 7 Pro 64bit

Labview 2012.

I tried to cast it to Excel.CustomProperties and Office.DocumentProperties and neither of those work.  There is a VI here (credit: Ben64) where he adds a VBA routine to a Word document via ActiveX and calls it to modify the custom properties.   

Technically RGT is just a bunch of ActiveX calls wrapped in nice, pre-packaged VIs. 

[ben64]

CustomDocumentProperties probably returns a variant because the custom properties can be of different type (text, number, date, yes or no). The following code should theoretically works. The type output can be use to convert the variant value to the proper datatype.

I specified theoretically because from the office pc the count property returns 0 Excel 2013) and from the home pc I get error 3008 (Excel 2010).

The Office.DocumentProperties class should be used, the Excel.CustomProperties class relates to worksheet object metadata.

Ben64

[zou]

I have Excel 2007.  I got error 3008 too.

[zou]

I'm not good at VB.

How do I return a string?

I tried this:

    Private Sub DefineXLProperties(Title As String, value As String by ref)

    value = ActiveWorkbook.CustomDocumentProperties.Item("Title")

    End Sub

and I got error: -2146827284 when I call "Excel Run Macro.vi"

[zou]

I simplified VBA code, still get same error.

Looks like VBA can't return a value.

[zou · Accepted solution]

I got it !

Since VBA Sub can't return a value, export it to clipboard.

However, the value is not updated from SharePoint Server ! 😞

When use "ContentTypeProperties" instead of "CustomDocumentProperties", it works !

and easy. 🙂  No VBA macro needed, just ActiveX.

[ben64]

This doesn't work for me. As you said a Sub can't return value so instead I had it wrote the custom properties to a new worksheet, got the data using Excel Get Data.vi and then deleted the new worksheet. A bit ugly and twisted but it works well to get all the custom properties.

Ben64