Jump to content
Sign in to follow this  
seangriffin

Excel COM Automation with no user interactivity errors

Recommended Posts

seangriffin

This is a small example of how I managed to automate Excel via the Excel UDF (COM Automation) whilst avoiding COM errors when the user interacts with Excel at the same time the script is running.

Though this might be useful because I've spent most of a day solving this, and I haven't seen a good solution in the forums yet.

This topic solves the COM errors that occur when a user interacts with an Excel workbook whilst it is being automated via the Excel UDF. These errors can occur when calling the _ExcelWriteCell function in a loop whilst the user clicks on cells in the worksheet, or selects different sheets. When such an error occurs the event is lost (the cell is not written for instance) and cannot be easily recovered.

This solution should work for Office 2003 and later, though has only been testing with Office 2003.

This example solves the "edit mode" bug described here:

And solves automation errors such as these:

This example requires a blank Excel workbook that is created and open with the following path:

c:excel_com_test.xls

When run, the script continuously writes cells to the Excel workbook. Interact with the workbook as the script is running (for example, click on cells, double-click on cells for edit mode, select other sheets). In all cases no data should be lost (all _ExcelWriteCell calls should be successful and every row in the worksheet should have an entry). If COM errors do occur they should not interrupt the UDF calls.

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>
#include <File.au3>

; Note - the Protect method below is also good, when used in combination with
; cell locking, but doesn't stop the user selecting sheets
; and causes COM errors and lost events.
;$excel.Activesheet.Protect("", True, True)

global $excel

; Enable an error handler to trap COM errors that occur
; when a user interacts with Excel whilst the script is
; running.

$oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

; Enable the CTRL+BREAK hotkey to stop the script
; (to simulate VBA).

HotKeySet("^{PAUSE}", "Quit")

; Create a GUI for the user to stop the script anytime.

GUICreate("Excel COM test", 130, 80, 1, 1, $WS_SYSMENU, $WS_EX_TOPMOST)
GUICtrlCreateLabel("Running...", 20, 5, 50, 20)
$stop_button = GUICtrlCreateButton("Stop", 20, 25, 80, 30)
GUISetState(@SW_SHOW)

; Excel setup

$excel = _ExcelBookAttach("c:excel_com_test.xls")
$excel.sheets("Sheet1").range("A1:A5000").clear

; Main Loop

for $i = 1 to 5000
  
   ; Check for a user GUI event

   $msg = GUIGetMsg()
  
   If $msg = $GUI_EVENT_CLOSE or $msg = $stop_button Then ExitLoop

   ; Lock Excel temporarily from user interactivity prior to the
   ;  script automating excel.  This ensures actions like
   ; selecting cells or selecting sheets doesn't cause a
   ; COM error and lost events.

   $excel.Application.Interactive = False
   $excel.Application.DisplayAlerts = False
  
   ; wait for the excel application to be ready to accept
   ; automation requests (Office 2003 and later)

   While $excel.Application.Ready = False
   WEnd
  
   ; Perform excel automation

   $excel.sheets("Sheet1").cells($i,1).value = "Demo Test Row " & $i

   ; Unlock Excel enabling user interactivity.  Give the user
   ; a short delay to perform an action in Excel (simulating
   ; the VBA DoEvents call).

   $excel.Application.DisplayAlerts = True
   $excel.Application.Interactive = True
   sleep(10)
Next

Quit()

Func _ErrFunc($oError)
  
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _      
        "err.windescription:" & @TAB & $oError.windescription & @CRLF & _         
        "err.description is: " & @TAB & $oError.description & @CRLF & _     
        "err.source is: " & @TAB & $oError.source & @CRLF & _         
        "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _         
        "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _     
        "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _         
        "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _         
        "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc

; This function restores user interactivity to Excel

Func Quit()
  
   $excel.Application.DisplayAlerts = True
   $excel.Application.Interactive = True
   Exit
EndFunc
Edited by seangriffin

Cheers, Sean.

See my other UDFs:

Chrome UDF - Automate Chrome | SAP UDF - Automate SAP | Java UDF - Automate Java Applications & Applets | Tesseract (OCR) UDF - Capture text from applications, controls and the desktop | Textract (OCR) UDF - Capture text from applications and controls | FileSystemMonitor UDF - File, Folder, Drive and Shell Monitoring | VLC (Media Player) UDF - Creating and controlling a VLC control in AutoIT | Google Maps UDF - Creating and controlling Google Maps (inc. GE) in AutoIT | SAPIListBox (Speech Recognition) UDF - Speech Recognition via the Microsoft Speech (SAPI) ListBox | eBay UDF - Automate eBay using the eBay API | ChildProc (Parallel Processing) UDF - Parallel processing functions for AutoIT | HyperCam (Screen Recording) UDF - Automate the HyperCam screen recorder | Twitter UDF - Automate Twitter using OAuth and the Twitter API | cURL UDF - a UDF for transferring data with URL syntax

See my other Tools:

Rapid Menu Writer - Add menus to DVDs in seconds | TV Player - Automates the process of playing videos on an external TV / Monitor | Rapid Video Converter - A tool for resizing and reformatting videos | [topic130531]Rapid DVD Creator - Convert videos to DVD fast and for free | ZapPF - A tool for killing processes and recycling files | Sean's eBay Bargain Hunter - Find last minute bargains in eBay using AutoIT | Sean's GUI Inspector - A scripting tool for querying GUIs | TransLink Journey Planner with maps - Incorporating Google Maps into an Australian Journey Planner | Automate Qt and QWidgets | Brisbane City Council Event Viewer - See what's going on in Brisbane, Australia

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  

×