Cell address of last cells filled in excel sheet

[jarrod]

When pulling cell content from Excel using LabVIEW, often as data has been added to a sheet, it may be addressed outside the cell coordinates previously employed for a successful import. Is there a way to programatically get the last row and/or column in an Excel sheet that has data occupying it?

[Michael_Munroe]

The UsedRange property of a Worksheet will return a Block that contains the last row and column information. You can use the Rows.Count and Columns.Count to get the total Rows and Columns used. This may not work perfectly if data has been deleted, but it always works when data has beed added. Try the attached vi (LV6, Excel 2000).

Michael Munroe
www.abcdefirm.com

[jarrod]

The attached vi was just what I was looking for, thank you. I attached a VI to take the count and convert it into column addressing for your liking.

[Michael_Munroe]

Thanks for the VI, but I rarely calclulate alpha-numeric cell addresses. I consider that to be a waste of computing power.

All of my VI's use a string address which typically contains a named range or "A1", and relative row and column addresses as integers. If I use a saved workbook as a template, I setup all the ranges with names to provide direct addressing as well as better tolerance of workbook modifications.

Michael Munroe
www.abcdefirm.com

[jarrod]

Thanks for the extra advice. Sorry the VI wasn't useful for you. I don't completely understand your comments though. I get spreadsheets from other departments,that my boss wants a report from, so I set up a VI to do the parsing and create another report. The VI you posted was very useful for handling other peoples updated spreadsheets, and I wanted a way to map it back to Excel and it seems that the property nodes accepting values like A1, or BC23 lead me to build that VI for the column portion of the string from your column output and concatenate the decimal string of the row address from your code, and the addressing worked well. Would it be too much to ask what you mean by using a "named range" and how that interfaces with the Active
X property nodes in LabVIEW or setting up the ranges for "direct addressing"?

[Michael_Munroe]

In my situation, the test station always records the same type of data with maybe a dozen header fields that describe the test setup. For each type of test, I create a master template workbook that I open, fill with data, print the report and then Save As a new file.

Rather than compress the data into the top left corner of the sheet, I space out the fields, give each one a descriptive title and make the whole page look more presentable.

In Excel, I use Insert->Name->Create to give the data locations unique names. In LabVIEW, I use the Range Names instead of cell references to save each data into the proper location. Just wire the name into the Cell1 input. Excel translates the Range Name into the corresp
onding cell location and directly addresses the proper cell. I also use relative row and column indexes to accomodate repeating data.

In the example attached, notice how "Fit_MSE" appears in Excel's cell address just above the "A" column instead of "C8". You can define new names by typing into this box directly.

The two benefits are that I don't have to keep track of each data's cell address, and if I make major edits to the template, I don't have to change my LabVIEW code. All Range Names are adjusted automatically when rows or columns are inserted.

Michael Munroe
www.abcdefirm.com