Modify

Opened 6 years ago

Closed 5 years ago

#2441 closed Bug (Works For Me)

_ExcelReadCell

Reported by: anonymous Owned by:
Milestone: Component: AutoIt
Version: 3.3.9.19 Severity: None
Keywords: Cc:

Description

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

Attachments (1)

test.xlsx (7.7 KB) - added by anonymous 6 years ago.

Download all attachments as: .zip

Change History (15)

Changed 6 years ago by anonymous

comment:1 Changed 6 years ago by water

  • Component changed from AutoIt to Standard UDFs

comment:2 Changed 6 years ago by water

  • Resolution set to No Bug
  • Status changed from new to closed

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

comment:3 Changed 6 years ago by Jpm

  • Resolution No Bug deleted
  • Status changed from closed to reopened

comment:4 Changed 6 years ago by 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

comment:5 Changed 6 years ago by 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.

comment:6 Changed 6 years ago by 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.

comment:7 Changed 6 years ago by water

  • Component changed from Standard UDFs to AutoIt

comment:8 Changed 5 years ago by 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.

comment:9 Changed 5 years ago by 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!

comment:10 Changed 5 years ago by 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)

comment:11 Changed 5 years ago by Jpm

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

comment:12 Changed 5 years ago by Jpm

@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

comment:13 Changed 5 years ago by 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.

comment:14 Changed 5 years ago by Jpm

  • Resolution set to Works For Me
  • Status changed from reopened to closed

Thanks, I close it s 3.3.13.1 is OK

Guidelines for posting comments:

  • You cannot re-open a ticket but you may still leave a comment if you have additional information to add.
  • In-depth discussions should take place on the forum.

For more information see the full version of the ticket guidelines here.

Add Comment

Modify Ticket

Action
as closed The ticket will remain with no owner.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.