This is the response that I received from Microsoft Tech Support. I hope it
helps someone in the same situation. My solution was to do the manipulation
within the same instance of Excel. Regards.
Excel 2003 behaves differently than previous versions regarding the 90
second timeout for OLE automation tasks. Applications work with older
versions of Excel, but when processing large data they fail when using Excel
2003 because the 90 second timeout expires, an error occurs in Excel, a
messagebox pops up, and no further processing is possible. this change
appears to have been an intentional design change for Excel. In order to
address several serious reentrancy issues that can occur when Excel is in an
outgoing COM call and a dialog is displayed, Excel intentionally disables
it's object model whenever it needs to display a dialog.
There are several work arounds, I think this will be the total solution
1. In Visual Basic there is a way to extend the timeout for OLE servers.
Code in VB is app.olerequestpendingtimeout = x
There is not an equivalent in VBA.
2. In VBA, put "Application.DisplayAlerts = False" as the first line of
codein his proc and "Application.DisplayAlerts = True" as the last line.
3. I notice our customer open several work book, so please also notice this:
The message occurs because you are Automating a second instance of Excel (or
any otherAutomation client) from within Excel,the Open method is taking a
long time to return to the calling instance so the calling instance of Excel
is indicating to the user that it is not hung but simply waiting for the OLE
Automated application to return.
In vb, set a reference to the current instance of Excel rather than creating
a new instance of it.
Public Sub cmdRunStrategies_Click()
Dim ExcelApp As Excel.Application
Set ExcelApp = Application
ExcelApp.Workbooks.Open
("C:\ODBCTest\WorkbookWithTimingLooopInOpenEvent")
End Sub
With VBA since it resides within the Application the reference to the
Application is not necessary for most commands so you could simply use:
Public Sub cmdRunStrategies_Click()
Workbooks.Open ("C:\ODBCTest\WorkbookWithTimingLooopInOpenEvent")
End Sub
