How to make sure reading an Excel cell won't lead to a crash

[frankinda]

Hi,

I have a problem with reading Excel cells.

I have a program which reads continually an Excel file (line by line) and update it (in functions of other events).

It can also be updated manualy.

The problem is that when a UF Excel function doesn't work, the AutoIt program ends on an error.

I've found a workaround of _ExcelBookAttach to make sure it works and I will be able to read a cell. The problem was to be sure I'm not editing a cell for example, which would have resulted in a crash.

My solution was to make an Autoit exe accepting as arguments the file I want to access.

$oExcel = _ExcelBookAttach($CmdLine[1])
If @error Then
    MsgBox(16 + 262144, "Error", "Unable to use the file")
    Exit
EndIf
_ExcelReadCell($oExcel, 1)
Exit

;return 0 if runs successfully, 1 otherwise

So I use it in another program with a personal function like that (if you have a better solution, please tell me) to be sure AutoIt won't crash:

Func openExcelFile(ByRef $oExcel, ByRef $ExcelFile)
    While RunWait('"' & @AutoItExe & '" /ErrorStdOut /AutoIt3ExecuteScript "' & "My_Exe_File" & '" "' & $ExcelFile & '"')
        MsgBox(16 + 4096 + 262144, "Error", "Unable to use the Excel file" & @LF & "Please check a cell is not being edited")
        openExcelFile($oExcel, $ExcelFile)
    WEnd
    $oExcel = _ExcelBookAttach($ExcelFile)
    If @error Then
        openExcelFile($oExcel, $ExcelFile)
    EndIf
EndFunc   ;==>openExcelFile

The problem is that I can't do a similar thing each time I want to read a cell, because it would slow too much my program!

For the moment, the program only crashes while I'm reading a cell. I use my openExcelFile defined above. And then I have a function which does some reading on the file depending on some values it reads (with _ExcelReadCell).

I have to find a workaround because my program will eventually stop at a random time (maybe it tries to read a cell while Excel is automatically making an autosave or another action). I want to make sure my reading of a cell won't lead to a crash of AutoIt.

A solution would be to make a program which reads a cell and returns it but first, it would be unefficient (would last a long time if I must run a program for each cell I read) and it would return 1 each time there is an error.

The return error message is :

C:\Program Files\AutoIt3\Include\Excel.au3 (694) : ==> The requested action with this object has failed.:

Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value

Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value^ ERROR

If you've already been confronted to this kind of problem, please tell me your solutions!

[frankinda]

Edit :

This problem seems to be more frequent when writing in an Excell cell (instead of reading).

So I updated my personal function and instead of trying to read a cell, it writes in a cell (a cell not used).

In general, if you know how to have Autoit script not crash (and go on), it would be great!

thanks

[Spiff59]

Look at the "COM Reference" in the helpfile, particularily near the end at "COM Error Handling".

That will avoid the hard crashes out of AutoIt and give you some information as to what your bug/conflict regarding Excel might be.

[frankinda]

Thank you very much!

I didn't know this existed... should solve my problems!