Jump to content
Sign in to follow this  
stealth

3.3.12 Pass Variable of Last Row in ColB to _Excel_RangeRead

Recommended Posts

While using 3.3.8, this worked fine:

Local $fFile = "C:\Users\" & @UserName & "\Documents\test.xlsx"
If Not FileExists($fFile) Then
MsgBox(0, "Excel File Test", "Can't run this script b/c you don't have " & @CRLF & $fFile & " open")
Exit
EndIf
 
$oExcel = ObjGet("", "Excel.Application")  ;Gets an existing Excel Object
 
If @error Then  ;Error traps if Excel file is not open
MsgBox(0, "ExcelFileTest", "You don't have Excel running at this moment. Error code: " & Hex(@error, 8))
Exit
EndIf
 
$oExcelDoc = ObjGet($fFile)  ;Get an Excel Object from an existing filename
$dDdoc = $oExcel.Sheets("Sheet1")  ;dim the Workbook's sheet
Global Const $xlUp = -4162  ;global constant for Excel up, finding last row
$LNC = $oExcel.Sheets("Sheet1").Range("B65535").End($xlUp).Row ;dims last row in Excel Col B

I'm unable to pass variable $LNC (last empty cell in ColumnB) to _Excel_RangeRead. What am I doing wrong?

Is Global Const $xlUp = -4162 still valid in 3.3.12?

$oAppl = _Excel_Open()
$sWorkbook = "C:\Users\" & @UserName & "\Documentstest.xlsx"
$oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Dim $LNC

Global Const $xlUp = -4162  ;global constant for Excel up, finding last row
$LNC = $oWorkbook.ActiveSheet.Range("B65535").End($xlUp).Row ;dims last row in Excel Col B
$sResult = _Excel_RangeRead($oWorkbook, Default, $LNC, 1)
MsgBox(0, "last row", $sResult)

Thank you in advance.

Edited by stealth

Share this post


Link to post
Share on other sites

What is the value of @error after _Excel_RangeRead?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@error 3

@extended -2147352567

$LNC = $oWorkbook.ActiveSheet.Range("B65535").End($xlUp).Row ;dims last row in Excel Col B
Msgbox(0, "last row", $LNC)

Correctly returns value of the last Cell in Column B. However, I'd like to pass the cell address as a variable to _Excel_RangeRead and that's where I'm stuck. 

Edited by stealth

Share this post


Link to post
Share on other sites

@error = 3 means: $vRange is invalid.

 

Correctly returns value of the last Cell in Column B.

As far as I understand your code does not return the value of the last cell in column B but the row number of the last cell in column B.

Could you please try:

$oAppl = _Excel_Open()
$sWorkbook = "C:\Users\" & @UserName & "\Documentstest.xlsx"
$oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Dim $LNC

Global Const $xlUp = -4162  ;global constant for Excel up, finding last row
$LNC = $oWorkbook.ActiveSheet.Range("B65535").End($xlUp)
$sResult = _Excel_RangeRead($oWorkbook, Default, $LNC, 1)
MsgBox(0, "last row", $sResult)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Great :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...