Sign in to follow this  
Followers 0
redrum

Issue: _ExcelReadCell - error on any keystroke entry

13 posts in this topic

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

Share this post


Link to post
Share on other sites



#3 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

You had breakfast right now? Fine ^_^

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Examples for a COM error handler can be found here.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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
Sign in to follow this  
Followers 0