Issue: _ExcelReadCell - error on any keystroke entry

[redrum]

Hello,

I need to figure out why this script exits as soon as I enter a keystroke in any cell of the sheet.

My Script application is continuously reading a single cell of an Excel worksheet. The Excel worksheet has macros that change the cell value as items are selected in the worksheet. All of this works properly. In other words, I can select various cells in the worksheet, the macros update the cell being read by the script, and the script continues to run and read the the specific cell.

Issue: Whenever a keystroke is entered in any cell of the sheet, including the cell being read, the script exits with an error. The error is from the _ExcelReadCell UDF.

I created the short script (attached) to illustrate the issue.

It seems there must be a simple explanation/fix for this, but I have not been able to figure it out - looking for some help on this.

Thanks,

redrum

Excel Read Testing.au3

[GMK]

The problem is that Excel VBA (COM) can not detect cell edit mode. Once you press F2 or start typing in the cell, $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value fails in the Excel UDF. I don't know of any way around it.

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/3333e18b-cef3-4d78-b47a-6916a1b2d84c/

[water]

Your problem is caused by a user editing a cell while Excel tries to access the sheet.

This site states:

"For example, Microsoft Excel rejects requests from a client application while a spreadsheet cell is being edited."

Use the latest AutoIt beta version and try this script:

; Script Start - Add your code below here
#include <Excel.au3>

Global $Cellvalue = ""
;Open workbook for testing the read of a cell
$oExcel = _ExcelBookNew()
;Write a value into cell
_ExcelWriteCell($oExcel, "Testing", 1, 1)
;Loop to read and display value of cell every second
While $CellValue <> "Stop"
    $CellValue = $oExcel.Activesheet.Cells(1, 1).Value
    If @error Then
        ConsoleWrite("A cell is being edited by the user!" & @CRLF)
    Else
        ConsoleWrite("Cell Value is = " & $CellValue & @CRLF)
    EndIf
    Sleep(1000)
WEnd
Exit

Edited August 27, 2012 by water

[redrum]

Thanks,

I'm going to give this a try.

You guy's are awesome, I make a post, go to breakfast, and have some direction when I get back!

Doesn't get any better - -

redrum

[water]

You had breakfast right now? Fine

It's 16:30 here and I will leave office in 2 minutes and grab my mountain bike

[redrum]

Results of using AutoIt Beta:

I am getting the same error and exit from script when using the suggested script (suggested earlier by water). I haven't used a Beta version before, but here is what I did:

Downloaded the Beta version

Went to the AutoIt under "All Programs" and clicked on Toggle Beta - got the message indicating "now using Beta version"

Opened up a new blank script and pasted in the recommended script and ran it.

When typing into a cell, the same error and exit from script occurs.

Hoping I did the Beta version correctly. Any other suggestions?

Regards,

redrum

[water]

To make sure you use the Beta version press Alt+F5 in SciTE to run your script. This should give you the following output on the SciTE console:

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /beta /ErrorStdOut /in "C:Temptest2.au3" /UserParams

+>09:19:54 Starting AutoIt3Wrapper v.2.1.0.8 Environment(Language:0407 Keyboard:00000407 OS:WIN_7/Service Pack 1 CPU:X64 OS:X64)

>Running AU3Check (3.3.9.5) from:C:Program Files (x86)AutoIt3beta

+>09:19:55 AU3Check ended.rc:0

>Running:(3.3.9.5):C:\Program Files (x86)\AutoIt3\Beta\autoit3.exe "C:Temptest2.au3"

+>09:19:56 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 4.485

[redrum]

Thanks Water,

The test script works for me now with Alt+F5

Now going back to fix my real script.

My initial script, when updated, will also run in the Beta mode if I use Alt+F5 (I don't need to use the "Beta Toggle")?

I can also compile it in Beta version?

redrum

[water]

To compile as beta use Alt+F7. You can check the available tools by clicking on the "Tools" menu in SciTE.

If you want to get the same result with the production version of AutoIt you need to add a COM error handler to your script.

[redrum]

I modified my script now and it is working fine. I'm just a little surprised at how long the "busy" lasts when a cell is changed - seems like up to 5sec - but that doesn't matter in my script usage.

I can continue to use the Beta version as long as I don't run into any other issues with that. If I do have issues with the Beta version, I will have to determine how to add the COM error handler - probably in the Forum somewhere. I'll deal with that if I need it.

I much appreciate your help on this, Water, I was really stuck and beginning to look for workaround ways to get the cell value into my script (like possibly using the clipboard came to mind)

Best Regards,

redrum

[water]

Examples for a COM error handler can be found here.

[redrum]

Hello,

I have been using a beta version for one of my scripts per this discussion last year.  I needed the Com error handler to allow changes to the Excel sheet while script was running and accessing it,.

Is it still necessary to use the beta version, or will the latest production version now do the Com error handling?

Thanks,

Doug

[water]

No new production version has been released since last year. So stick with whatever version works for you.