Jump to content
Sign in to follow this  
Soulstorm

$xlCellTypeLastCell not working in excel Workbook

Recommended Posts

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

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

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

 

Share this post


Link to post
Share on other sites

Everything is working just fine here.

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
6 minutes ago, Danp2 said:

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

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

Share this post


Link to post
Share on other sites
2 minutes ago, water said:

Everything is working just fine here.

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

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

 

Share this post


Link to post
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

(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 :D (the second option was unprotect, not as funny)


All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...