What ActiveX properties/methods will convert the name for a range of Excel cells into a Range that can be used to access the cells?

[DAD]

I have a commercial app that generates an Excel spreadsheet that I want to work with in LabVIEW.

The spreadsheet contains cells with data validation configured to specify a list of allowed values.  When you click on a cell, it behaves like a LabVIEW ring and lets you pick from the list.

The list of possible values for a cell is specified as a named ranged of cells on another sheet.  I can get the formula specifying the name of the list, but am still puzzling out what properties/methods will allow me to convert the name into something that will let me reference the cells so I can extract the values and configure a LabVIEW Listbox or ring with the same options.

Please see the VI (LV 2010) and spreadsheet (Excel 2007) in the attached .zip.

Thanks in advance for any information that will shorten the time to figure this out.

[Andrew_B]

Hello DAD,

This is Andrew Brown, an Applications Engineer from National Instruments. After researching methods of accessing Named Ranges (created within Excel 2007 or later), I discovered a useful website on Defined Names, specifically the section called Working With Names in Visual Basic.

Working with the syntax from that website, I was able to find named ranges based off the index (their name). I modified your example program to include this functionality for A1, and attached it to this post as "Sample Spreadsheet modified.zip". 

Please let me know if you have related questions or issues. Thanks, and have a great day!

Regards,

Andrew Brown 

[DAD]

Andrew,

Thanks for the link.  I took a quick look but will go back to it later.

My first attempt to run the modified code resulted in an unknown error.  I took a look at the modifications and noticed it was possible for the _workbook Close invoke node to execute before the nodes to resolve the name execute.  I rearranged the error flow to serialize the operations and prevent this.

Now it runs without error and is giving me the information I was looking for.

Thanks again!