Jump to content

Excel.au3 error when using _Excel_RangeFind


 Share

Recommended Posts

Hi Guys,

I have been using Autoit for a while now and I recently started using it for Excel.

However, I have a major problem that I was not able to resolve by just using the search option in the forum.

My code goes like this:

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\test.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
Local $aResult = _Excel_RangeFind($oWorkbook, "test")
_ArrayDisplay($aResult)
_Excel_Close($oExcel)

And in my script dir I have the simplest test.xlsx file (attached for reference).

When I try to run my script, it gives me an error message:

C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR
->04:41:34 AutoIt3.exe ended.rc:1

I am also attaching the Excel.au3 file for your reference.

FYI, I have tried the latest stable and beta version of autoit, but they both don't work for me. Currently I am with the beta.

Could you please assist me with this?

Best regards,

Punko

test.xlsx

Excel.au3

Link to comment
Share on other sites

IIRC correctly this is a bug in the _Excel_RangeFind function as it is missing the COM error handler.
Either establish a global COM error handler or modify the Excel UDF and copy the lines from other functions of the UDF.

; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError

 

Edited 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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Thanks, water.

However, I get this:

#include <Excel.au3>
Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
MsgBox(0, "", "AutoitVersion: " & @AutoItVersion)
$oExcel = _Excel_Open()
$sWorkbook = @ScriptDir & "\test.xlsx"
$oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
$word = "test"
$aResult2 = _Excel_RangeFind($oWorkbook, $word)
_ArrayDisplay($aResult2)
_Excel_Close($oExcel)
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
Endfunc

Error attached.

Same goes if I test it with the beta version of autoit.

Any help would be appreciated.

Error handler.jpg

Link to comment
Share on other sites

In this case it is a general error telling you that the cell range has no name. This means: The error should be ignored.
So I suggest to add the lines I posted in post #2 to the UDF. This is how the bug is fixed in the next beta version anyway.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Hmm I believe I finally got it to work.

After I got home today I looked at the Excel.au3 file and added the lines (they were not there) and it gave me an error at line# 663.

I commented it and the function is now working perfectly!

Thank you so much for your support.

Link to comment
Share on other sites

Glad you got it working :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...