How to get Excel Last Author document property?

[GerdW]

Hi,

I'm looking for a way to get the "Last Author" information of the BuiltInDocumentProperties of an Excel workbook.

When reading that information LabVIEW delivers a variant datatype. How to access the particular entry in this list?

What do I have to do to access the items of the DocumentProperties as listed here?

[JohnMc19]

Hi GerdW,

Have you tried using the 'Variant to Data' or the 'Variant to Flattened String' functions? You can use them to get the data you have into a manageable format.

Hope this helps!

[GerdW]

Hi John Mc,

I tried VariantToData with an array of strings, but failed.

What kind of datatype do I have to convert to? How do I tell LabVIEW 'Convert to Microsoft.Office.Core.DocumentProperties.Item(System.Object)!'?

[JohnMc19]

Hi GerdW,

Could you attach your code please? I want to try and replicate your code and it will make it easier for me to help!

Regards,

[GerdW]

Hi John Mc,

here you go (with LV8.5)...

[Wayne.C]

GerdW,

Used a function from OpenG lib.  Looks like it is a refnum.  Still unsure how to use it.

[GerdW]

Hi Wayne,

I doubt Excel will give you a TD enum.

But anyway: right now I can't test this as VIPM can't connect to the internet on my company's computer to download the OpenG toolkits...

[GerdW]

simply bump...

Someone with an idea?

[DavidJCrawford]

Hi Gerd,

I think you could do it but you would need to call a macro that inserts the answer into a cell on a worksheet. You could then access that cell using the range object within labview. It will be a wee bit messy as you will probably need to flip between two work books. The first workbook would contain the macro and would be used to store the answer in a cell. The other workbook is the one that needs to be Activated when you run the macro.

I suggest that you could call a macro that accesses the ActiveWorkBook.BuiltInDocumentProperties

This is from from excel vba help for BuiltinDocumentProperties

rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.BuiltinDocumentProperties
    Cells(rw, 1).Value = p.Name
    rw = rw + 1
Next

Based on this, I tried the following code to access a single property which seemed to work.

Private Sub DisplayBuiltinDocProperties(intMyItem As Integer)
   On Error Resume Next
   Worksheets(1).Activate
   Cells(1, 1).Value = Empty
   Cells(1, 2).Value = Empty
   Cells(1, 1).Value = ActiveWorkbook.BuiltinDocumentProperties(intMyItem).Name
   Cells(1, 2).Value = ActiveWorkbook.BuiltinDocumentProperties(intMyItem).Value

End Sub

You could have this macro in an excel file that you open and run the macro from.

You will also need to make sure that you have the correct workbook activated e.g.  

Workbooks("BOOK1").Activate

If a BuiltInDocProperty is not set excel will raise an error. I suggest you use the On Error to handle that error to stop the code hanging in excel

If you need more help see an old post of mine to learn how to call an excel macro from inside excel.

Hope this help you on your way.

David

[TroyK]

Sorry to resurrect such an ancient thread but I've just recently tried to get this information from about 350 excel files on network shared directories.

I can't believe it's so hard to get data from .NET dynamic COM-objects in LabVIEW!

I gave up in the end and found that it was easy to do in powershell.

http://stackoverflow.com/questions/5645991/find-with-powershell-the-last-saved-by-of-word-and-excel-...

The problem is that it would take forever using powershell (or any other method of opening and manipulating an instance of Excel) to read this single property from ~350 excel files.

It then occurred to me that Windows seems to do it quickly and easily without opening the Excel application. They must extract the information directly from the file.

Fortunately for me all the excel files I wish to read are in the new format .xlsx which is actually a plainly zipped bunch of XML files. The "Last Author" property is stored in:

  > docProps > core.xml > XML tag  "cp:lastModifiedBy"

I've attached an example of using OpenG ZLIB functions and a "Match Pattern" to extract the information directly without having to create temporary files or open instances of Excel.

Note: It will not work if the workbook is open in Excel. Excel exclusively locks the file and it seems the OpenG ZLIB function tries to open it with write access.