_ExcelReadCell

[anonymous]

i tried to read cell value from attached excel file with code:

#include <Excel.au3>
$oxlsx= _ExcelBookOpen(@ScriptDir & '\test.xlsx')
_ExcelSheetActivate($oxlsx,'test')
$cell_1 = _ExcelReadCell($oxlsx,1,3)

and

result:
!>11:17:17 AutoIt3.exe ended.rc:-1073741819

thanks and sorry for my english

[water]

You don't get a result from cell A3 (row 1, column 3) because the formula in this cell returns division by zero.
A1: 1, B1: 0, C1: =A1/B1

[psandu.ro]

yes, you are right but my problem is when in excel is an "error", my script is stopped with this error:
AutoIt3.exe ended.rc:-1073741819

thanks

[water]

I reduced the example to:

$oExcel = ObjCreate("Excel.Application")
$oWorkbook = $oExcel.WorkBooks.Open(@ScriptDir & '\test.xlsx')
$sValue = $oWorkbook.Activesheet.Range("C1").Value
MsgBox(0, "Value C1", $sValue)

Works with 3.3.8.1 and crashes with 3.3.9.21.

[water]

When translated to VBS you get a COM error: "Type mismatch", 0x800A000D

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Temp\test.xlsx")
Wscript.Echo "Value: " & objExcel.Cells(1, 3).Value

So - for me - it looks like a problem with the COM error handler of 3.3.9.21.

[water]

Version 3.3.11.3 handles the invalid cell value like version 3.3.8.1 did: It does not crash.
3.3.8.1 returned an invalid object.
3.3.11.3 returned keyword NULL.
But AutoIt never raises the COM error "Type mismatch", 0x800A000D like VBS does.
If this is desired behaviour then this ticket can be closed.

[jchd18]

I don't know how easy/hard it might be but returning NULL doesn't seem appropriate: returning #INF and raising @error just like AutoIt 1/0 does. #IND would be fine for indeterminate values.

Forget if that's too much to ask and don't count me in: I don't even have Office installed!

[water]

3.3.11.4 with the rewritten Excel UDF returns NULL (displayed result of IsKeyword is 2). Example:
#include <Excel.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & '\test.xlsx')
$cell_1 = _Excel_RangeRead($oWorkbook, "test", "C1", 1, True)
ConsoleWrite(IsKeyword($cell_1) & @LF)

[J-Paul Mesnage]

@water
any reason why adding _Excel_Close($oExcel) is not closing
adding _Excel_BookClose() does not help

[J-Paul Mesnage]

@water
the _Excel_Close() is working in 3.3.13.1
I m just wondering if the COM error in _Excel_Open() can be avoid

#include <Excel.au3>

Global $iEventError = 0 ; to be checked to know if com error occurs. Must be reset after handling.
Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ; Install a custom error handler

; Create application object
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & '\#2441 _ExcelReadCell crash.xlsx', Default, Default, True)
Local $sCell_1 = _Excel_RangeRead($oWorkbook, 'test', "C1", 1, True)
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $sCell_1 = ' & $sCell_1 & @CRLF & '>Error code: ' & @error & '    Extended code: 0x' & Hex(@extended) & @CRLF) ;### Debug Console
;~ _Excel_BookClose($oWorkbook) ; And finally we close out
_Excel_Close($oAppl) ; And finally we close out

; This is a custom error handler
Func ErrFunc()
	Local $HexNumber = Hex($oMyError.number, 8)
	MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
			"Number is: " & $HexNumber & @CRLF & _
			"WinDescription is: " & $oMyError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.source is: " & @TAB & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext & @CRLF & _
			"err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & "0x" & Hex($oMyError.retcode) & @CRLF & @CRLF)
	$iEventError = 1 ; Use to check when a COM Error occurs
EndFunc   ;==>ErrFunc

[anonymous]

The COM error displayed by _Excel_Open is caused by function ObjGet.
The function first tries to "attach" to a running Excel instance. If none is found the COM error pops up and Excel is being started by ObjCreate.
You can avoid the problem by using parameter $bForceNew = True to always start a new Excel instance.

[J-Paul Mesnage]

Thanks, I close it s 3.3.13.1 is OK