Programmaticaly Get Next Cell Index of Excel Sheet

[CAN Programmer]

Hello,

I was hopeing if any one can help me on this

I am trying to add a section to my Labview code that uses ActiveX to access information in an Excel sheet in the following manner.

Search a column programmatically for the next cell that contains data and return the row index. I did that by going through the column, cell by cell, then get the data length, if the length is zero then its empty and I should keep indexing until I find one that is not emply. This method works however it is slow.

Does any one know if there is any faster method to do that?

Thanks,

Ayman

[Justin D]

Hi Ayman,

I'm not really sure if there is a faster way to do this with ActiveX.  If you are optimizing for speed I would reccommend using something other than xls files to store your data.  You could store everything in a csv file and load it all into a big array in your program.  This way any operations on it would be really fast and it would be easier to work with.

-Justin D

[Michael_Munroe]

ActiveX allows all of the same functionality you get when you operate Excel interactively.  From the keyboard you would press control-down arrow to jump to the first non-blank cell or the last non-blank cell.  The same function expressed as ActiveX is:

ActiveCell.End(xlDown).Activate

Then read ActiveCell.Row

[Ben]

Hi Michael et al,

I am trying to translate

"

ActiveCell.End(xlDown).Activate

Then read ActiveCell.Row

"

Into LV code and have not found anything that looks like that.

What kind of reference do I start with, Sheet ?

is it all done with property nodes or are invoke nodes involved?

Thank you,

Ben

[Ben]

Expanding on my previous....

I have to add a new line of data to a spreadsheet when something happens.

I have to open the sheet,

find the first blank cell in collumn A

and add the new data there.

I want to avoid reading each cell one after the other to find out where the first blank is.

The above post sounds ideal if I knew how to construct it.

Thank you,

Ben

[Ben]

bump

[shoneill]

Ben,

this might help you further.

I did these VIs a while back, I can't remember which Excel version they use, but it might just work today.

Regarding the cryptic numbers, they're the numerical representations of "Xldown" and so on.  I had to look them up in Excel.....

Shane.

EDIT: This gives you the last similar cell.  If your starting cell is full, it gives you the last non-empty cell in the chosen direction.  If your cell is empty, it gives the last empty cell.....

Message Edited by shoneill on 02-19-2007 03:33 PM

[Brian_Beal]

A solution I've used in the past involves using the Excel "COUNT" function in a cell that looks at a certain column.  I then read the value of COUNT, and know where I can insert the next row.

[Ben]

Thank you Shane and Brian.

Ben

[Joe_H]

Ben,

on the excel board i just posted an example of how to add a row into and excel spreadheet.

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