Excel Get Data- Defining cells

[MogaRaghu]

I got a VI from this forum to read data from specified cell range in XLS. Works fine.

I have modified it to read data from just one sheet ( attached )

However before I read the XLS file I may not always know the number of rows in the file. I would hence like to programatically find out this from the chosen XLS file and feed it to the reading VI.  The Active-X exposes too many methods and I am not sure which one does what ..;-(

Any help is welcome.

[ben64 · Accepted solution]

Use Worksheet UsedRange property. On the returned range object use the Range Rows property. And finally on the returned range object use the Range Count property. This will give you the last row of the worksheet.

Ben64

[Bob_Schor]

If you have the Report Generation Toolkit (which began to be bundled in starting with LabVIEW 2014), it is much easier to use its functions to do what you want.  Here's an example:

The first function, File Dialog, lets you select a file of the type .xls or .xlsx.  It passes the file to New (Excel) Report, where it creates the Report Object (the Green "chain" wire).  The next function is Get Worksheet -- you can wire in an Index (default is 0) or a Sheet Name (I just put it in to show you how to use it).  The next function is Get Last Row, which returns the last row used in the WorkSheet.  However, you can see we don't need it -- the following function, Get Data, returns all of the data in the Worksheet in the specified 2D String array, after which we close Excel (and the file).  The Array will have the correct number of rows and columns automatically -- the only "catch" is that all of the data will be the string representation that you would see when you viewed the Worksheet -- if rows or columns represented numerical data, you'd need to do the conversion yourself.

[Of course, if the entire WorkSheet was, say, Dbl, you could wire a 2D Array of Dbl to the Get Data function and you'd have the data as Dbls].

Bob Schor

[MogaRaghu]

Hi Ben,

Thanks - your  three pronged property step did the trick and its working as expected. Attaching the new code - possibly this is what you meant !

But this also raises a basic question on such software usage which are not very obvious to the user.  Is there any documenation on these kind of things or one has to generally go by an intution and maybe in some cases by trial and error ?

[MogaRaghu]

Hi Bob,

Your suggestion to use the Report Generation tool kit of course was pretty simple to use and I got what i wanted in a few steps as described by you.

I had to do a small manipulation to avoid reading the whole XLS file as you can see from the attachment. I would have gone with this solution had I been allowed to use the LV2015. The customer wants only LV2012 and in that the Report genration tool kit is not free.

Reason why I did not mark this as accepted solution. Sorry !

[Bob_Schor]

No problem.  Since I didn't know the precise requirements of your Excel "Read", my demo code gave you all the "pieces" you would need to "do it yourself", as you clearly did.  Something else to know about, if you are doing a lot of Excel stuff, is the pair of functions found on the Excel Advanced Palette that let you go from "Excel Coordinates" (such as B3) to Row/Column, handling the Offsets and "transpose" issues for you.

Bob Schor

[ben64]

---

MogaRaghu wrote:

But this also raises a basic question on such software usage which are not very obvious to the user.  Is there any documenation on these kind of things or one has to generally go by an intution and maybe in some cases by trial and error ?

---

You need some knowledge  of the Excel ActiveX object model. Search the MSDN website for Excel Developer Reference.

Ben64