Waiting for another OLE action to complete? (Excel 2000, Access 97)

Topic

New Reply

Hi there…

I’m trying to automate the creation of a named range (no problems there) and the import of that named range into an Access database… It’s working… However, it takes a long time on the transferspeadsheet line, and I get a message (at least once… sometimes 2 or 3 times in a row) that says:

“Microsoft Excel is waiting for another application to complete an OLE action”

Any ideas? I’d appreciate any help in how to stop this message and/or figure out what’s making it run sooooo slowly at this point…
Here’s my code… I’ll bold the line that triggers this message…
__________________________________________________________________

Option Explicit

‘Author: Trudi
‘Date: August 12, 2005
‘Last Updated: August 22, 2005
‘Purpose: To extract Real Return Bond indexed prices to update prices in the data

Sub SendPrices()

‘Call procedure that writes the data to a separate table for export
Write_RRB_Prices (no need to post this code… works perfectly)

‘Call procedure that exports the prices to the Access database
ExportPrices (“I:homeRRB_PriceUpdates.mdb”)

End Sub

Function ExportPrices(strDatabase As String)
On Error GoTo ErrHandler

Dim appAccess As New Access.Application
Dim strRange As String
Dim strTable As String

SetPricesRange

‘Set values for string variables
strRange = “RRBPricesTable”
strTable = “Prices_RRB”

‘Open the database
appAccess.OpenCurrentDatabase strDatabase
On Error Resume Next
‘Delete the old table
appAccess.DoCmd.DeleteObject acTable, strTable
‘Import the “RRB_PricesTable” named range into the database
On Error GoTo ErrHandler
appAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strTable, ActiveWorkbook.FullName, True, strRange
‘Copy this table with the date (file name) added to table name
appAccess.DoCmd.CopyObject , “Prices_RRB_” & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) – 4), acTable, “Prices_RRB”

ExitHandler:
‘Close the database
appAccess.Quit acQuitSaveNone
‘Release the memory for the object variable (we’re done with it)
Set appAccess = Nothing
‘If the process has gotten this far, it has completed successfully… Message user…
MsgBox “Prices data exported successfully!”, vbInformation, “Process Complete”
Exit Function

ErrHandler:
‘Give user a message with the error description
MsgBox “ExportPrices procedure ended with the following error – ” & Err.Description, vbExclamation
‘Continue with exit of function
Resume ExitHandler

End Function

Reply | Quote

Replies

Hello Trudi,

Long time no see!

It could have to do with the Excel 2000/Access 97 combo, but more probably it’s because you let Access import a range from the currently open workbook. If you could run the code from another workbook, and close the workbook with the range to be transferred, execution might be faster. It might also help if you could run the whole operation from Access.

Reply | Quote

Thanks Hans!

Yes, it’s been a while… Surprisingly, I’ve been doing well all by myself… BUT I always know where to find the experts when I need them…

I’ll give your ideas a try… Thanks again…

Reply | Quote

I would expect you would get much better performance using ADO and a proper SQL string to get the data over to Access.

Reply | Quote