How to extract a complete column from excel?

[Achim]

Hi there,

I have a excel file with some testing parameters, organized in rows. That means, in the first column are the serial numbers (type ID) of my "devices under test", and the respective parameters are located in the adjacent columns of the row.

I'd like to extract the first column as a whole with my LabVIEW application, search this column (1D array)  for a particular number and use the "found"-index to read the rest of the row to get the parameters associated with this index/serial number.

How can I do this? Until now I always had to read a specific range e.g. "A1:E8" to get values out of an excel file. But as I don't know how many serial numbers are stored in the excel file, I don't know how to limit the range...?!

Does anybody have an idea how to solve this problem?

Regards
Achim

[vt92]

One workaround would be to create a user-defined function which searches for the first empy cell, something like:

Function FindLastCell()
  Dim LastCell As Range
  With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
    If IsEmpty(LastCell) Then
      'do nothing
    Else
      Set LastCell = LastCell.Offset(1, 0)
    End If
  End With
  FindLastCell = LastCell.Row
End Function

Insert this function in your spreadsheet at a known location, then read the value.  The value will indicated the number of rows that are populated before you hit the empty cell.  You can edit the function for the appropriate column.




Message Edited by vt92 on 01-31-2008 08:14 AM

[smercurio_fc]

That method requires modification of the spreadsheet. This will be cumbersome to do and likely impractical if the spreadsheet is created "on the fly".

It seems like you're really just interested in finding out the last row used in column A. In that case, you can use a similar procedure. I'm assuming you're already using ActiveX to interface to Excel, so attached is a sample program (LabVIEW 8.2) that will return the last row used in column A.