Read all cells from an Excel file?

[Markhl]

Hi!

I'm trying to read every cell from an Excel spreadsheet file, e.g.
test.xls. The example in the NI Dev Zone (ActiveX Read Cells from
Excel2000 VI) works if you know in advance what the first and last cell
are, e.g. A1 and E453.

The trouble is that the number of rows & columns in the file will vary;
the last cell of 1 file might be E453, another might end at F29.

How can I programmatically read every cell in the file? I thought of 2
approaches:

1: Find an ActiveX property that returns the last cell (or last row and
last column).
2: Use some other way to read an .xls file, maybe not by launching Excel
but by using another program ...

The NI applications engineer couldn't find a reasonably easy solution,
so I'd w
elcome any input.

Thanks! Mark

[Kamran S.]

Mark,

I haven't been able to find out if there is an ActiveX property or method the excel exposes to be able to do this ... searching on http://msdn.microsoft.com didn't return anything particularly useful.

I've tried this sort of thing a couple of different ways. The simplest is if I have control over the file generation code, in that case I've just used the first row A1 and B1 to contain the number or rows and columns respectively and moved on from there.

If that isn't a possibility then you could enter nonsense data around the acutal data and then look for that and then resize the array. This could get pretty slow if you read row by row, I've somtimes read chuncks of rows and then adjusted the next read and and combined arrays. With a large data set the memory and s
peed implications do need to be looked at.

The last thing is what you are referring to in 2. You should be able to save the .xls file as a comma delimited file. In this case the "Read for Spreadsheet File" vi should be able to just read all the data in. I'm not sure how easy it is to export an .xls file to a comma delimited format programatically.

I think this should work all though I haven't tried it. If it doesn't then you could just read one row and then count the number of commas to determine the number of columns. You can then read the entire file and convert the data into an array, as every comma would separate each column and a carriage return would seperate each row.

Haven't got LabVIEW on this machine so if I find anything new tomorrow I'll post again.

Kamran

[CraigGraham]

The "Cells" property of the "Worksheet" object returns a "Range" object of
all the cells used. As with any other Range object, the value property
returns a variant that can be converted within Labview to a 2D array of
whichever datatype.

Note that you're screwed if the spreadsheet contains any invalid cells,
since (at least in v5) Labview's "Convert to G Data" function fails when it
encounters an invalid element.

Mark Hanning-Lee wrote in message
news:3A886C26.7A62@syagen.com...
> Hi!
>
> I'm trying to read every cell from an Excel spreadsheet file, e.g.
> test.xls. The example in the NI Dev Zone (ActiveX Read Cells from
> Excel2000 VI) works if you know in advance what the first and last cell
> are, e.g. A1 and E453.

[km76107]

Hi Mark,

Have u been able to figure out the solution to read all cells from an excel file? I am having the same problem and I would appreciate if you could share your ideas, if you have got your vi working.

Thanks

[Goldchip]

Hi,

Not sure if you got an answer to this query, but you can download
examples of writing to, and reading from Excel - see the downloads
section of our website.

You can specifiy individual cells if you need to.

Regards,

Chris Harden.

Goldchip.

Suppliers of low cost, NIDAQ compatible, add-on hardware products.

www.goldchipDAQProducts.com
info@goldchipDAQProducts.com

kmoorthy76107 wrote in message news:<506500000005000000EB6A0000-1016899309000@exchange.ni.com>...
> Hi Mark,
>
> Have u been able to figure out the solution to read all cells from an
> excel file? I am having the same problem and I would appreciate if you
> could share your ideas, if you have got your vi working.
>
> Thanks