Soulstorm Posted September 23, 2019 Share Posted September 23, 2019 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 Link to comment Share on other sites More sharing options...
water Posted September 23, 2019 Share Posted September 23, 2019 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Soulstorm Posted September 23, 2019 Author Share Posted September 23, 2019 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 Link to comment Share on other sites More sharing options...
Soulstorm Posted September 23, 2019 Author Share Posted September 23, 2019 I don't seem to be able tu use this "SpecialCells($xlCellTypeLastCell)" function in: $oRange = $oWorkbook.Sheets(1).UsedRange.SpecialCells($xlCellTypeLastCell) Link to comment Share on other sites More sharing options...
Danp2 Posted September 23, 2019 Share Posted September 23, 2019 Have you tried using "ActiveSheet" instead of "Sheets(1)"? Latest Webdriver UDF Release Webdriver Wiki FAQs Link to comment Share on other sites More sharing options...
water Posted September 23, 2019 Share Posted September 23, 2019 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Soulstorm Posted September 23, 2019 Author Share Posted September 23, 2019 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)) Link to comment Share on other sites More sharing options...
Soulstorm Posted September 23, 2019 Author Share Posted September 23, 2019 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..) Link to comment Share on other sites More sharing options...
water Posted September 23, 2019 Share Posted September 23, 2019 (edited) 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Soulstorm Posted September 23, 2019 Author Share Posted September 23, 2019 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. Link to comment Share on other sites More sharing options...
water Posted September 23, 2019 Share Posted September 23, 2019 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
seadoggie01 Posted September 23, 2019 Share Posted September 23, 2019 (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) 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 functionsAcro.au3 UDF - Automating Acrobat ProToDo Finder - Find #ToDo: lines in your scriptsUI-SimpleWrappers UDF - Use UI Automation more Simply-erKeePass UDF - Automate KeePass, a password managerInputBoxes - Simple Input boxes for various variable types Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now