LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Excel collection variant

Using Excel XP ActiveX. The BuiltInDocumentProperties property call from the workbook object returns a variant that is supposed to be "the entire collection of built in document properties". My problem is that I can't get the data out of the variant. I've tried using variant to data (with types cluster, array, string) and variant to flattened string, to no avail. Everything I've tried results in nothing coming out or an undefined error. Any ideas?

Thanks,
Dave.
==============================================

0 Kudos
Message 1 of 6
(3,033 Views)
Hi Dave,
I understand that you're using the BuiltInDocumentProperties property of a workbook and unable to access the data. Technically, this is more of a microsoft/visual basic issue than a LabVIEW (LV) issue.
Even so, using the variant to data VI is the right way to get the builtInDocumentProperties of the excel workbook. The key is to specify the correct data type for the variant to data VI. The best source to find this data type is in the Microsoft Visual Basic Help files which you can access within Excel under Tools >> Macro >> Visual Basic Editor.
If you look in the help file, you will find that builtInDocumentProperties returns a collection of Document Property Objects. The first step to access the document property object is to feed in a workbook referen
ce as the data type for the variant to data type VI. After you provide the correct data type, you will be able to create a property node to get each item from the collection of document property objects. In this case, the item is a document property. Once you have the item, you can create a property node to get the value of the item.
I recommend trying to access the workbook properties in this manner. There are several resources which will help in this task:
1. Visual Basic Help will provide descriptions of the excel data types as well as example macro programs which you can use to model your LV code.
2. The Microsoft Developer Network (MSDN) may have useful information.
3. If you search for the functions in Google, you may find examples that better relate to what you're trying to do.

If none of these resources help, and you are still unable to implement your LV code, you can post a simplified version of your code along with a description of the errors (including numbers) that y
ou are getting.

Hope this helps!

Marcus G.
National Instruments
0 Kudos
Message 2 of 6
(3,033 Views)
I don't think the Workbook reference is the right one. I think I've figured out that the object needed is a DocumentProperties object from the Office library (NOT the Excel library). The problem is that when I then try to use a property node, I get the following error: "Error 3008 occurred at Automation Interface for the specified class cannot be obtained. Check if the automation refnum type is the correct class for this operation in Built_In_Document_Properties.vi" I've attached an example.

Thanks ahead,
Dave.
==============================================

0 Kudos
Message 3 of 6
(3,033 Views)
Dave,
Your code is password protected, so I'm not able to review it. Even so, I tried implementing what you are doing on my own machine without success. I can neither read nor modify a DocumentProperties object. I can modify other visual basic objects, but not the documentProperties.
My recommendation is that we try to implement a work around. The easiest work around is to use an excel macro to get the workbook's Document Properties and import them into LabVIEW. The macro code is already provided in the Visual Basic help file for builtInDocumentProperties:

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

If you are unfamiliar with how to call an ex
cel macro from LabVIEW, there is an example program that can guide you. You can find the program on our website at this url:
http://sine.ni.com/apps/we/niepd_web_display.display_epd4?p_guid=B45EACE3D9D856A4E034080020E74861&p_node=DZ53003

Please let me know if this doesn't help. Thanks and good luck!

Marcus G.
National Instruments
0 Kudos
Message 4 of 6
(3,033 Views)
Oops, sorry about the password. Here's a clean copy for you to look at.

I'm having the same issue. I have no problem using/modifying other Excel objects (range, worksheet, workbook, etc.). I'll let you know how the macro thing works out.

Dave.
==============================================

0 Kudos
Message 5 of 6
(3,033 Views)
Dave,
I took a look at your code, and you're implementing it the right way. There seems to be some problem interfacing with the builtInDocumentProperties object. This simply bolsters my recommendation that you should try running the Visual Basic Macro to get the document properties from the workbook.
Let me know how that works out.
Regards,
Marcus G.
National Instruments
0 Kudos
Message 6 of 6
(3,033 Views)