I have an Excel VB routine which loops through multiple workbooks opening and
saving/closing them as it goes. This worked fine in Excel 2000. In Excel 3003
I get messages from the save command that Excel is waiting for another
application to complete an OLE process.

"JackL" wrote:

I have an Excel VB routine which loops through multiple workbooks opening and
saving/closing them as it goes. This worked fine in Excel 2000. In Excel 2003
I get messages from the save command that Excel is waiting for another
application to complete an OLE process.

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\WorkbookWithTimingLooopInOpenEven t")
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\WorkbookWithTimingLooopInOpenEven t")
End Sub



"JackL" wrote:

I have an Excel VB routine which loops through multiple workbooks opening and
saving/closing them as it goes. This worked fine in Excel 2000. In Excel 3003
I get messages from the save command that Excel is waiting for another
application to complete an OLE process.

JackL wrote:
I have an Excel VB routine which loops through multiple workbooks opening and
saving/closing them as it goes. This worked fine in Excel 2000. In Excel 3003
I get messages from the save command that Excel is waiting for another
application to complete an OLE process.

Does this mean that Excel will still be buggy 1000 years from now? You
must have a really eary beta version. I pity our descendants :)