Jump to content

Excel Write Cell


Recommended Posts

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,

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by nanthiran2005
Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...