Excel Write Cell

[nanthiran2005]

Hi guys,

I have been writing into excel cell in a loop by incrementing the row for every loop. The excel value have been written in background as im using the excel object to write in. While I'm writing in, Im actually performing some test with a software, and thus writing the result "OK" or "NG" in excel result column. However, in the middle of the loop, AutoIt gives me error message stating that "The operation with this object is failed". I have tried both UDF and my own coding. But this problem seems to be persisting and it happens at random loop count. Any idea why this issue? Could it be because of Excel lags or any other reason?

Regards,

[Clark]

I would say "any other reason".

But seriously, very difficult to determine the issue without seeing some code.

[water]

Do you have a COM error handler in your code to grab more error information?

[nanthiran2005]

Clark: This is the portion of my coding where I call this function to write in the cell. The column is predefined globally while the row number is updated everytime after calling this function. The Excel is launched before running this script. Note that the error is occuring at random times. Thank you in advance

Global $oExcel2 = ObjGet("C:Userssivan_nDesktopresult.xlsx","") ; location of result file
Local  $expMem = "16"
local $getMem = ControlCommand("", "", "", "GetSelected", "")
if $getMem=$expMem Then ; if correct
$res = "OK"
Else
$res = "NG"
EndIf
writeValueResult($expMem,$getMem,$res)
$rROWNUM+=1  

func writeValueResult($expVal,$getVal,$res)
_ExcelWriteCell($oExcel2, "Value", $rROWNUM, 3)
_ExcelWriteCell($oExcel2, $expVal, $rROWNUM, 4)
_ExcelWriteCell($oExcel2, $getVal,$rROWNUM, 5)
_ExcelWriteCell($oExcel2, $res, $rROWNUM,6)
With $oExcel2.Worksheets(1).Cells($rROWNUM,6)
if $res = "OK" then
.Interior.ColorIndex=4
Else
.Interior.ColorIndex=3
EndIf
EndWith
EndFunc

Edited February 13, 2012 by nanthiran2005

[nanthiran2005]

Water: I tried using COM error handler and it just refer back to the same line error that AutoIt showing as well. The only different is that error handler giving some error in numbers.

[water]

Could you please post the output of the COM error handler? The error number and text is what should help to find the problem.

[nanthiran2005]

Water: This is the error i get this time, but I'm unsure if this is the error i get all the time as I cant capture the error more than one time since yesterday. And also, I didnt halt the program. Thanks in advance.

err.number is: -2147418111

err.windescription: Call was rejected by callee.

err.description is:

err.source is:

err.helpfile is:

err.helpcontext is: 2097213

err.lastdllerror is: 0

err.scriptline is: 451

err.retcode is: 0

[Clark]

Maybe you could try creating the Excel spreadsheet from within your script, and then saving it manually result.xlsx.

func ExcelPrint($InArray)

$sFilePath = @TempDir & "Temp.xls"

If Not _FileCreate($sFilePath) Then ;Create an .XLS file to attach to

MsgBox(4096, "Error", " Error Creating File - " & @error)

return 1

EndIf

_ExcelBookOpen($sFilePath)

$oExcel = _ExcelBookAttach("Temp.xls", "FileName") ;with $s_mode = "FileName" ==> Name of the open workbook

_ExcelWriteSheetFromArray($oExcel, $inArray, 1, 1, 0, 0)

EndFunc

[nanthiran2005]

Thank you for the suggestion. But just to check with you, do I create this temporary path at the beginning of the programme? And also I dont use array to store my data as I'm writing it into excel in real time for every loop of testing.

[Clark]

@TempDir is a system variable, so you shouldn't need to do anything.

The code was for example purposes. In your case you wouldn't use the _ExcelWriteSheetFromArray function.

[water]

If you search for "excel 0x80010001 rejected" you will get a lot of hits.

The only thing that looks like a "solution" was found here. They suggest to retry the last call. So if the COM error handler returns -2147418111 then just retry the last Excel call.

Or you could give this a try. Lock Excel as long as you do your updates

$oExcel2.Application.IgnoreRemoteRequests = True

and unlock it later.

Please check this

[nanthiran2005]

Thank you for the help guys, I have tried to do the excel spreadsheet as Clark mentioned but it is still not solving the problem. And as Water advised, I have put that line of coding as well. But the problem still persisting. This time I found the real error message that always appear which is "800A03EC". When I googled about this issue, I realise that there could be alot of reasons for this error. One that I doubt could cause this problem in my coding is calling of the excel property. Any idea on how to solve this problem? I read in another article that creating a wrapper might help, but I have no idea how to create a wrapper function in AutoIT.

[nanthiran2005]

• http://mcoxeter.wordpress.com/2011/03/25/excel-com-issue-solved/
• http://blog.hoegaerden.be/2008/07/27/excel-automation-the-cultureinfo-bug/

Attached is the article regarding Excel wrapper that I said Edited February 15, 2012 by nanthiran2005