Excel/VBScript & LastRow Function

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/VBScript & LastRow Function

Post by Tourless » Mon Sep 23, 2019 7:01 pm

Hi Folks,

In excel vba projects I often use a function to find the last row of data on a given sheet...

Code: Select all

Function LastRow(targetSheet As Worksheet, Optional targetCol As String = "A")

    With targetSheet
        LastRow = .Cells(.Rows.Count, targetCol).End(xlUp).row
    End With

End Function
I'm having a hard time figuring out how to transpose this into something usable within my script. Here's what I'm working with currently...

Code: Select all

'Retrieves a cell value from the specified worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Dim xlLastRow
  Dim xlColumn
  
  Set xlSheet = xlBook.Worksheets("MySheet")
  Set xlColumn = Column
  Set xlLastRow = xlSheet.Cells(.Rows.Count, xlColumn).End(xlUp).Row
  GetCell = xlSheet.Cells(Row, Column).Value
End Function
While I've tried a few variations and encountered a few errors, my current error with the above code is an Object Required error. It doesn't know what 'column' is.

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

Re: Excel/VBScript & LastRow Function

Post by Dorian (MJT support) » Tue Sep 24, 2019 11:23 am

If you're looking to find the last row of data, then XLGetSheetDims is the command you need. The following script will work on the sample Excel file included with Macro Scheduler.

Code: Select all

//Set the variables
Let>ExcelPath=%script_dir%\Samples
Let>ExcelFile=example.xls
Let>SheetName=Sheet1

//Check xls file exists (in Samples folder)
IfNotFileExists>%ExcelPath%\%ExcelFile%
  MDL>Cannot find %ExcelPath%\%ExcelFile%%CRLF%Do you have scrips samples installed?
  Exit
Endif

//Open Excel
XLopen>%ExcelPath%\%ExcelFile%,1,xlBook
waitwindowopen>example.xls*

//get sheet Dimensions
XLGetSheetDims>xlBook,SheetName,Rows,Cols

//Get value from last row, Col A
XLGetCell>xlBook,SheetName,%Rows%,1,LastRowColA

//Message popup
mdl>The last row is %Rows%.  The value of A%Rows% is %LastRowColA%

In addition in case this helps anyone else, once you know the sheet dimensions, you can of course now make a nice loop which ends at the last row.

Code: Select all

Let>RowNum=1
Repeat>RowNum
  Let>RowNum=RowNum+1
  
  XLGetCell>xlBook,SheetName,RowNum,1,##
  XLGetCell>xlBook,SheetName,RowNum,2,##
  XLGetCell>xlBook,SheetName,RowNum,3,##
  XLGetCell>xlBook,SheetName,RowNum,4,##
  XLGetCell>xlBook,SheetName,RowNum,5,##
  XLGetCell>xlBook,SheetName,RowNum,6,##
  XLGetCell>xlBook,SheetName,RowNum,7,##

Until>RowNum,Rows
Yes, we have a Custom Scripting Service. Message me or go here

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

Re: Excel/VBScript & LastRow Function

Post by Tourless » Fri Sep 27, 2019 2:33 pm

Brilliant! Now I've got two new scripts that interact with excel files that auto-update upon opening. It goes like this...
The script opens the Excel file. Excel updates itself and the script takes a handful of variables from one of the sheets. The script then launches a production program, logs in, enters the variables into the program, saves and closes the production program, then excel, then emails me once it's complete.

If anyone is interested I'll post the complete code.

Thanks as always!

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

Re: Excel/VBScript & LastRow Function

Post by Dorian (MJT support) » Mon Sep 30, 2019 11:19 am

You're welcome.

And we're always happy to see any examples or samples that may help other users. Plus it's always interesting to see how other people do things.
Yes, we have a Custom Scripting Service. Message me or go here

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