Excel with Variable Rows

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Tourless
Pro Scripter
Posts: 69
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

Excel with Variable Rows

Post by Tourless » Wed May 10, 2023 7:41 pm

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?

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Excel with Variable Rows

Post by Dorian (MJT support) » Wed May 10, 2023 8:13 pm

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.
Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
cron
Sign up to our newsletter for free automation tips, tricks & discounts