redrum Posted August 27, 2012 Share Posted August 27, 2012 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, redrumExcel Read Testing.au3 Link to comment Share on other sites More sharing options...
GMK Posted August 27, 2012 Share Posted August 27, 2012 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/ Link to comment Share on other sites More sharing options...
water Posted August 27, 2012 Share Posted August 27, 2012 (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 August 27, 2012 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 27, 2012 Author Share Posted August 27, 2012 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 Link to comment Share on other sites More sharing options...
water Posted August 27, 2012 Share Posted August 27, 2012 You had breakfast right now? Fine It's 16:30 here and I will leave office in 2 minutes and grab my mountain bike My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 27, 2012 Author Share Posted August 27, 2012 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 Link to comment Share on other sites More sharing options...
water Posted August 28, 2012 Share Posted August 28, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 28, 2012 Author Share Posted August 28, 2012 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 Link to comment Share on other sites More sharing options...
water Posted August 28, 2012 Share Posted August 28, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 28, 2012 Author Share Posted August 28, 2012 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 Link to comment Share on other sites More sharing options...
water Posted August 28, 2012 Share Posted August 28, 2012 Examples for a COM error handler can be found here. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted November 11, 2013 Author Share Posted November 11, 2013 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 Link to comment Share on other sites More sharing options...
water Posted November 11, 2013 Share Posted November 11, 2013 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now