Seeking General Advice for Avoiding Automation Errors in Windows 7 ?

[StillUsingNotes]

For seven years, we have been running a wonderful report-creation program frequently. It use the COM interface to automate Excel. It runs for many hours, so debugging is difficult.

Ever since we moved up to Windows 7, it has started to pop up "Automation Error" boxes after several hours of running. It does this sporadically ; the exact same report may run OK on the next try. (We have confirmed that repeatedly.)

I am looking for general advice about possible causes for these, and advice for further analysis, and advice for possible workarounds.

The main user believes that his usage of Excel itself during the run may tend to cause these errors. We do find that running it overnight on an untouched machine, it does not bomb off. Unfortunately, we need to run it ASAP when we run it, and, the user must use Excel constantly all day.

The COM code creates its own instance of Excel, so it "should not" have any interaction with his foreground Excel session. OTOH, we have found that similar code running on Windows Server 2008 was bombing until we tweaked a "desktop" configuration for Excel, a well-known kluge which is detailed elsewhere.

The code that automates Excel via COM is in LotusScript, and it runs in the "background" of the Lotus Notes client.

 

[Tom Urtis]

Hard if not impossible to say without seeing any code or understanding the circumstances of why something errors, but when run again does not error. Could be API code not agreeing with a 32 or 64 bit version of Office or any number of reasons. I will say, my experiences with Windows7 have been pretty good, but again, really hard to offer any concrete suggestion with how many unknown moving parts there probably are to the project.

 

[ZVI]

Hi,

The COM code creates its own instance of Excel, so it "should not" have any interaction with his foreground Excel session.

This can be false assumption.

At excel automation the hidden instance of excel application is created.
For example via CreateObject("Excel.Application")

But if other instances are closed (by user) and user tries opening Excel file via explorer then that hidden instance is hijacked and becomes visible.
And what if user then quit Excel? Yes, your automation will hung because the previously hidden instance disappears.
Even if Excel is not quited but in dialog then automation is impossible.

Apply checking of instance in automation code like this:

  If objExcel Is Nothing Then
    Set objExcel = CreateObject("Excel.Application")
  ElseIf objExcel.Visible Or Not objExcel.Ready Then
    Set objExcel = CreateObject("Excel.Application")
 End If

Regards

 

[StillUsingNotes]

Thanks, this is great info that i never suspected.

I am considering a workaround. The app could frequently call a subroutine which will : save the open excel file, drop the old excel instance, make a new instance, open the saved file, and adjust any runtime settings or variables that keep the "current" state. Assuming that user foreground actions are the problem, would you expect this to help, or, expect it to encounter the same problem? It seems to me it should have the same problem.

In any case, this info enables us to justify a separate machine to run these reports. Thanks again!

 

[ZVI]

This loads Test.xls to the hidden instance of Excel via LoadHidden.vbs

' VBScript code of LoadHidden.vbs
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
With objExcel
  .Visible = False
  .Workbooks.Open ("C:\Temp\Test.xls")
  .Run "ThisWorkbook.Workbook_Open"
End With

The code of ThisWorkbook module of Test.xls looks as follows:

' Code of Thisworkbook nodule of Text.xls
Option Explicit
Dim WithEvents App As Application
 
' Provide Excel application subclassing
Private Sub Workbook_Open()
  Me.IsAddin = True ' <-- Make thisworkbook invisible
  Application.Visible = False
  If Not App Is Application Then MsgBox Me.Name & " is loaded"
  Set App = Application
  App.EnableEvents = True
End Sub
 
' Prevent the closing of ME, in automation set objExcel.Enableevents = False before closing
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
  Cancel = Wb Is Me
End Sub
 
' Open workbook in another instance of Excel
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
  If Not Wb Is Me Then OpenInAnotherInstance Wb
End Sub
 
' Close visible workbook and open it in another instance of Excel
Private Sub OpenInAnotherInstance(Wb As Workbook)
  Dim AppFullName As String, WbFullName As String
  With App
    If .Visible Then .Visible = False Else Exit Sub
    AppFullName = .Path & "\Excel.exe"
    WbFullName = Wb.FullName
    .EnableEvents = False
    Wb.Close False
    On Error Resume Next
    Shell """" & AppFullName & """ """ & WbFullName & """", vbNormalFocus
    .EnableEvents = True
  End With
End Sub

Loading workbook from explorer into the hidden instance of Excel triggers App_WorkbookOpen code.
Then that workbook is closed and reloaded in the new instance of Excel.

 

[StillUsingNotes]

Cooool! Since I have never seen these techniques before, I want to check, are these true?
--the purpose of LoadHidden.vbs is to test the code in the lower section easily
--another test would be to open an excel file from Windows Exploder
--the lower section is code which goes into the report Excel file that is created by the report program

Question: will the usual irritations with security and Excel macros occur, even if the report program starts with a new blank Excel file, and then emplaces the code into the file? We can reduce the Excel security level for these users; our default is "no macros".

 

[ZVI]

Cooool! Since I have never seen these techniques before, I want to check, are these true?
1 --the purpose of LoadHidden.vbs is to test the code in the lower section easily
2 --another test would be to open an excel file from Windows Exploder
3 --the lower section is code which goes into the report Excel file that is created by the report program

Question: will the usual irritations with security and Excel macros occur, even if the report program starts with a new blank Excel file, and then emplaces the code into the file? We can reduce the Excel security level for these users; our default is "no macros".

Answers:

1. Yes. In the 1st post you’ve mentioned automation of Excel from COM interface. Script of VBS file simulates such automation.
2. Yes
3. Yes

As to the macro security - at automation a workbook is loaded with macro enabled irrespective to the macro security level of Excel.
And installed Excel AddInses are not auto loaded in this case, but Com AddInses only.