$xlCellTypeLastCell not working in excel Workbook

[Soulstorm]

Hello everyone!

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object or connect to an already running Excel instance
Local $oExcel = _Excel_Open()
$workBookList=_Excel_BookList()
Local $oWorkbook =_Excel_BookAttach ($workBookList[0][1],"filename")

$oWorkbook.Sheets(1).Range("B22").Activate
$LastRow = $oWorkbook.Sheets(1).Range("B22").SpecialCells($xlCellTypeLastCell).Row

>"C:\Users\xxxx\Downloads\install\SciTe\..\autoit3.exe" /ErrorStdOut "X:\Abteilungen\Industrialisation\Oeffentlich\xxxxx\201704 xxxxx\02_Aufgaben\Scripts\xxxxxx\Excel-SAP.au3"    
"X:\Abteilungen\Industrialisation\Oeffentlich\xxxxx\xxxxx\02_Aufgaben\Scripts\xxxxx\Excel-SAP.au3" (10) : ==> The requested action with this object has failed.:
$LastRow = $oWorkbook.Sheets(1).Range("B22").SpecialCells($xlCellTypeLastCell).Row
$LastRow = $oWorkbook.Sheets(1).Range("B22")^ ERROR
>Exit code: 1    Time: 2.522

I tried almost everything and looked everywhere on the forum and I can't pinpoint the issue in my code.

 

I have an excel Workbook which is already open, prior to the script.

I then list all the available excel workbooks (there is only one).

I create my Workbook object (using the filename) and focuses on the cell "B22" (It works!).

And now I want to simply find the last used cell in column B.

But for some reaso, it won't work.

 

For some really weird reasons this work perfectly on another script, the only difference is that I use an excel file which not already opened:

$oExcel = ObjCreate("Excel.Application")
    $oBook = $oExcel.Workbooks.Open("I:\Oeffentlich\xxx\BDE\Datenstruktur BDExxx.xlsx")
   $oSheet = $oBook.Activesheet
   $oExcel.Visible = True
   $LastRow = $oBook.ActiveSheet.Range("B1").SpecialCells($xlCellTypeLastCell).Row + 1
    ;$oBook.ActiveSheet.Range("B"&$LastRow).Activate

 

[water]

That's described here in the second example: https://www.autoitscript.com/wiki/Excel_Range#Last_Cell

[Soulstorm]

Thanks for your answer!

 

Unfortunately I already tried this:

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object or connect to an already running Excel instance
Local $oExcel = _Excel_Open()
Local $aWorkBooks = _Excel_BookList()
Local $oWorkbook =_Excel_BookAttach ($aWorkbooks[0][1],"FileName")
$oWorkbook.Sheets(1).Range("B22").Activate
$oRange = $oWorkbook.Sheets(1).UsedRange.SpecialCells($xlCellTypeLastCell)

With the same error in the end:

"X:\Abteilungen\Industrialisation\Oeffentlich\0xxxxx\xxxxxx\02_Aufgaben\Scripts\xxxxx\Excel-SAP.au3" (9) : ==> The requested action with this object has failed.:
$oRange = $oWorkbook.Sheets(1).UsedRange.SpecialCells($xlCellTypeLastCell)
$oRange = $oWorkbook.Sheets(1).UsedRange^ ERROR
>Exit code: 1    Time: 1.644

 

[Soulstorm]

I don't seem to be able tu use this "SpecialCells($xlCellTypeLastCell)" function in:

$oRange = $oWorkbook.Sheets(1).UsedRange.SpecialCells($xlCellTypeLastCell)

 

[Danp2]

Have you tried using "ActiveSheet" instead of "Sheets(1)"?

[water]

Everything is working just fine here.

So my next question is: Which version of Excel do you run?

[Soulstorm]

  On 9/23/2019 at 3:26 PM, Danp2 said:

Have you tried using "ActiveSheet" instead of "Sheets(1)"?

Expand  

yup!

It's weird because:

$oWorkbook.Sheets(1).Range("B22").Activate

works perfectly!

But

$oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)

does not! (with ActiveSheet and Sheets(1))

[Soulstorm]

  On 9/23/2019 at 3:32 PM, water said:

Everything is working just fine here.

So my next question is: Which version of Excel do you run?

Expand  

Excel 2013, in German (if it makes a difference..)

 

[water]

Can you please add a COM error handler so we get more detailed error information. At least the line and function marked with "<=="

#include <Excel.au3>

; https://docs.microsoft.com/en-us/office/vba/api/excel.xldirection
Global $xlToRight = -4161
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Local\test.xlsx")
Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")  ; <== 
Local Const $xlUp = -4162
With $oWorkbook.ActiveSheet ; process active sheet
    $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells
    $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column
    $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number
    ConsoleWrite("Last used cell in column B: " & $iLastCell & @CRLF)
EndWith

;-----------------------
; COM Error handler      <==
;-----------------------
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

BTW: I'm running Excel 2016 in German

Edited September 23, 2019 by water

[Soulstorm]

Oh thanks water.

once again!

 

Excel-SAP.au3 (11) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Ausnahmefehler aufgetreten.

    err.description is:     Dieser Befehl kann für ein geschütztes Blatt nicht verwendet werden. Sie müssen zuerst den Schutz des Blatts aufheben (Registerkarte 'Überprüfen', Gruppe 'Änderungen', Schaltfläche 'Blattschutz aufheben'), um diesen Befehl zu verwenden. Sie werden möglicherweise aufgefordert, ein Kennwort einzugeben.
    err.source is:      Microsoft Excel
    err.helpfile is:    xlmain11.chm
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  11
    err.retcode is:     0x800A03EC

It all makes sense right now.. my excel file was protected.

[water]

That's the very first time I've seen a COM error message fully describing the problem and what to do to solve it

[seadoggie01]

(For those of us who can't speak German) 

"This command can not be used for a protected sheet. You must first clear the sheet's protection (Review tab, Changes group, Bleed sheet button) to use this command. You may be asked to enter a password."

But I think the "bleed sheet button" translated wrong  (the second option was unprotect, not as funny)