"Waiting for another application to complete an OLE transaction"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there!

We have a Microsoft SQL 2005 Express database that imports data from MS
Excel 2003 workbooks (MS Office 2003 Pro). The process is as follows: while
running the database, we open the workbook to be imported. We click on the
"Import" button in our database application, which then imports selected
ranges from the Excel workbook. When the database has the data it requires,
the workbook is closed automatically. Fairly regularly we receive the error
message "Microsoft Excel is waiting for another application to complete an
OLE action" at the time the workbook gets closed. Excel does not exactly
'hang' (crash) but is completely unuseable, and must be terminated by means
of the Task Manager. The database doesn't appear to suffer unduly from these
errors - the workbook can be opened again and imported (usually
successfully). Our PCs are running Windows XP Pro SP2.

I can discern no pattern to these errors - they are sporadic, and occur
irrespective of whether the information in the workbook may have been altered
or not, and / or saved or not. They occur in about 1-2 of every 10 such
imports. They are a problem due to the high number of workbooks we must
import each day.

I would really appreciate someone being able to tell me the cause of these
errors, and (more importantly!) - their resolution. Performing a web search
returned no helpful results.

Thanks very much!! :)
 
Sorry - I forgot to add that the workbooks that are imported are all
identical in structure - only the information changes.
 
Philip
It sounds like there is no code is the Excel workbooks, is that right?
How are you importing the data is is through DTS?
It really sounds like your import is triggering Excel to ask another
application something
so you could be triggering some event VBA code in the workbook or there may
be some issues with whatever you are using to connect to Excel. Are you
using ADO?
I know there used to be memory leak issues using ADO to query an open
workbook, but I think that got fixed by 2003.
Could you post the DTS script? it may be some tatty VB script in that?
Are there any unusual add-ins running in your Excel instance?

Sorry I can't be more helpful, when 2 apps don't play nice together its
often hard to pinpoint which is causing the problem. Have you tried posting
on a SQL server board?

cheers
Simon

Blog: www.smurfonspreadsheets.net
Website: www.codematic.net Excel development, support and training
 
Thanks for your response - this is a great service for us users!

To my knowledge, the workbooks were constructed from a single clean, blank
workbook, and thus contain no code or macros. I also do not have any unusual
add-ins installed (just some of the standard ones that come with Excel).

Unfortunately, my knowledge if SQL, etc is close to zero - I just use the
application. I am quite happy working with Excel and familiar with most of
the funtions, etc - but not macros. I asked the maintainer of our database
your questions and elicited the following responses:

The database uses direct access to Excel by OLE. The workbooks are closed in
the same manner that a user would use (the close / exit command). It does not
use DTS (and therefore I assume there is no code I can supply), but it does
use ADO.

I'm sorry I can't supply more info - this is simply NOT my area of
expertise! :)

As for posting to an SQL server board, I have not (yet!) - perhaps you can
suggest a sub-group for me to start with?

Again, thankyou very much for your help!

--
Philip



Simon Murphy said:
Philip
It sounds like there is no code is the Excel workbooks, is that right?
How are you importing the data is is through DTS?
It really sounds like your import is triggering Excel to ask another
application something
so you could be triggering some event VBA code in the workbook or there may
be some issues with whatever you are using to connect to Excel. Are you
using ADO?
I know there used to be memory leak issues using ADO to query an open
workbook, but I think that got fixed by 2003.
Could you post the DTS script? it may be some tatty VB script in that?
Are there any unusual add-ins running in your Excel instance?

Sorry I can't be more helpful, when 2 apps don't play nice together its
often hard to pinpoint which is causing the problem. Have you tried posting
on a SQL server board?

cheers
Simon

Blog: www.smurfonspreadsheets.net
Website: www.codematic.net Excel development, support and training
Click to expand...
 
Back
Top