Excel.au3 error when using _Excel_RangeFind

[PunkoHead]

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

[water]

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 November 26, 2015 by water

[PunkoHead]

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.

[water]

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.

[PunkoHead]

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

[PunkoHead]

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.

[water]

Glad you got it working