PunkoHead

Excel.au3 error when using _Excel_RangeFind

7 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

The problem is that the Excel.au3 already contains these lines in the RangeFind function.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Glad you got it working :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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