Excel COM Automation with no user interactivity errors

[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.

expandcollapse popup

#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 May 22, 2012 by seangriffin