Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks Hans! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Yes, it's been a while... Surprisingly, I've been doing well all by myself... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> BUT I always know where to find the experts when I need them... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    I'll give your ideas a try... Thanks again...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    I would expect you would get much better performance using ADO and a proper SQL string to get the data over to Access.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •