Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

Excel Write Cell


  • Please log in to reply
12 replies to this topic

#1 nanthiran2005

nanthiran2005

    Seeker

  • Active Members
  • 7 posts

Posted 13 February 2012 - 03:09 AM

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,







#2 Clark

Clark

    Prodigy

  • Active Members
  • PipPipPip
  • 162 posts

Posted 13 February 2012 - 06:14 AM

I would say "any other reason".

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

#3 water

water

    ?

  • MVPs
  • 15,097 posts

Posted 13 February 2012 - 06:22 AM

Do you have a COM error handler in your code to grab more error information?
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#4 nanthiran2005

nanthiran2005

    Seeker

  • Active Members
  • 7 posts

Posted 13 February 2012 - 07:06 AM

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

Plain Text         
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 by nanthiran2005, 13 February 2012 - 07:16 AM.


#5 nanthiran2005

nanthiran2005

    Seeker

  • Active Members
  • 7 posts

Posted 13 February 2012 - 07:08 AM

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.

#6 water

water

    ?

  • MVPs
  • 15,097 posts

Posted 13 February 2012 - 07:15 AM

Could you please post the output of the COM error handler? The error number and text is what should help to find the problem.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#7 nanthiran2005

nanthiran2005

    Seeker

  • Active Members
  • 7 posts

Posted 14 February 2012 - 01:20 AM

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

#8 Clark

Clark

    Prodigy

  • Active Members
  • PipPipPip
  • 162 posts

Posted 14 February 2012 - 01:21 AM

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

#9 nanthiran2005

nanthiran2005

    Seeker

  • Active Members
  • 7 posts

Posted 14 February 2012 - 03:29 AM

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.

#10 Clark

Clark

    Prodigy

  • Active Members
  • PipPipPip
  • 162 posts

Posted 14 February 2012 - 05:08 AM

@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.

#11 water

water

    ?

  • MVPs
  • 15,097 posts

Posted 14 February 2012 - 07:46 AM

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 thread.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#12 nanthiran2005

nanthiran2005

    Seeker

  • Active Members
  • 7 posts

Posted 15 February 2012 - 04:58 AM

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.

#13 nanthiran2005

nanthiran2005

    Seeker

  • Active Members
  • 7 posts

Posted 15 February 2012 - 05:14 AM

Attached is the article regarding Excel wrapper that I said

Edited by nanthiran2005, 15 February 2012 - 07:20 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users