Sign in to follow this  
Followers 0
stealth

3.3.12 Pass Variable of Last Row in ColB to _Excel_RangeRead

7 posts in this topic

#1 ·  Posted (edited)

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

@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)
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Sorry, typo on my part. "Correctly returns Row Number of the last Cell in Column B."

Let me try your suggestion. Thanks

Share this post


Link to post
Share on other sites

Thank you, Water! It worked

Share this post


Link to post
Share on other sites

Great :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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  
Followers 0