Read Excel spreadsheet from Excel 2000 and/or 2003

[TroyK]

Thanks Trey, that's is what I did. It was step 1. The big step was getting LabVIEW to ignore ActiveX methods it didn't recognise.
It's pretty much all good now.

Solution: Dynamically call by path a vi hidden in a .LLB with the extension renamed to .LLLB. Include the .LLLB file in the project as a support file.

This solution suits me at the moment.

I would, however, like to investigate smercurio_fc's suggestion of reading the data out of excel using an ODBC server. But I don't have time to play around with it at the moment.

Message Edited by Troy Kujawski on 05-22-2007 10:31 AM

[mikeporter]

Whoo! Whoo!!

The accessing Excel files through ODBC is your basic piece o' cake... Did a little poking and found two significant webpages.

The first shows how to create and Excel file and define a range in it that the driver can realte to like a database table. While the second shows how to read an Excel file and extract data from it. Many thanks to Alexander Mikula the author of both pieces.

Well, being the kind to want to try things out, the two attached files are LV implementations of the techniques he describes - and they really work slick.

Mike...

PS: As a side note the logic in these VIs would also work to access ANY database through the ADO interface. The only thing that would need to change is the connection string... Just thought you might be interested...

[TroyK]

Cooool, so now all I have to do is make sure all my excel spreadsheets have a named range called 'readrange' or something like that so I can open it as my 'recordset'.
That isn't tooo painful I guess.

[TroyK]

OK, maybe not so great.
It doesn't seem to be able to extract out the result of some excel formulas... such as concatenate() - which I use extensively.
I'd have to give different names to ranges in each sheet of a workbook if I wanted to read multiple sheets.

Great idea, not quite as fantastic as I originally thought though.

Looks like I'll be sticking to my original hack.

[Joe_H]

Great tips Mike,

I have posted this thread to the excel board.

For more information and some sample VI's and tool kits, you can go to the excel board

[mikeporter]

Would it work to have the calculation in one cell and have the cell you read be defined as equal to the one containing the calculation? For example, have the calculation in cell H5 and have the cell you're wanting to read be defines as "=H5"?

Mike...

[Brian_Beal]

One thing that should work is to use a conditional disable structure.  Check for the Excel version and then enable the proper ActiveX calls.  It will compile with the problem code disabled.

[TroyK]

Is there a way to programmatically change the value of a project symbol? If not, how can I change which case executes based on the version of excel on the target PC.

[TroyK]

I tried doing the calculation in one cell then reading a cell that points to it (as suggested by Mike earlier) but it doesn't work.

I think the reason it works in ActiveX but not through ODBC is that ODBC can't resolve all of the formulas that Excel can perform. You have to open an instance of Excel to allow it to compute all of the cell results, then read the results. That is why there is no easy way to directly read a .xls file without Excel.

[TroyK]

Update for those of you trying this in LabVIEW 8.5...

I recently updated to LabVIEW 8.5 and got some problems with my multi-version Excel activeX vis.
The 'Open VI Reference' function for a vi contained in a .LLLB file works in the development environment...
BUT... It no longer works when the application is compiled into an exe!
It can't seem to find the vi contained in the .LLLB file.

How I got it going again...

1. Make an identical copy of the '.LLLB' file except renamed with the standard '.llb' file extension.
     - Keep both files in the same directory!!!

2. Have your calling vis look for the standard '.llb' file (so it works in the development environment)

3. Include the .LLLB file in the project
4. Under the 'Source Files' category, add the .LLLB file to the  - 'always included' section in the application's properties
5. Then under the 'Source File Settings' category, select the .LLLB file and check the 'Rename this file in the build' checkbox.
     - make it's new name have the standard '.llb' extension.

Et viola, it's works again!

The invalid activeX control is hidden from the compiler, but works when on a system that has a different version of Excel activeX.


Message Edited by Troy K on 01-23-2008 04:27 PM