3.3.12 Pass Variable of Last Row in ColB to _Excel_RangeRead

[stealth]

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 November 18, 2014 by stealth

[water]

What is the value of @error after _Excel_RangeRead?

[stealth]

@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 November 17, 2014 by stealth

[water]

@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)

[stealth]

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

Let me try your suggestion. Thanks

[stealth]

Thank you, Water! It worked

[water]

Great