Excel with Variable Rows

[Tourless]

Hi Folks.

I searched around but couldn't find an answer... I'm going to be working with an excel sheet that will have variable rows. My data will always be in columns B, F, and O, starting at row 14, but the last row of data will always be a mystery. I'll be reading this data into my macro and writing it to a SQL DB. I know with Excel I can use VBA to find the lastRow but looking over the Excel Functions and some other MS functions I'm not sure if I can accomplish that.

XLRun and XLRunCode look like they will run the macro or VBA only if it's in the book referenced in XLOpen or XLCreate, but what about my personal.xlsb? Is my best bet to try and use VB Script Commands? What are my options here?

[Dorian (MJT support)]

XLGetSheetDims will tell you what the last row and column is.

Code: Select all

XLOpen>%USERDOCUMENTS_DIR%\mybook.xls,1,xlBook
XLGetSheetDims>xlBook,Customers,rows,cols

So in the example above if the sheet "Customers" has data up to F30, for example, rows will return 30, cols will return 6 (F being the 6th column).

All Excel functions require a handle created by XLOpen, XLCreate, or XLGet (for a workbook that's already open.

Usage examples for you to play with can be found at the above link.