How to deal with Microsoft Excel is waiting for another application to complete an OLE action

0 votes

When automating other MS-Office applications with excel, I frequently get ok-only prompts saying that Microsoft Excel is waiting for another application to complete an OLE action.

This only happens when automating lengthy tasks.

How can I deal with this in an appropriate fashion?

Two recent examples (I recon the code is less important):

  • creating an accdb-Database from Excel with an Access.Application and populating it by running rather complex SQL-queries on large amount of data.

    Public Function createDB(pathDB As String, pathSQL As String) As String
        Dim dbs As DAO.Database
        Dim sql As String
        Dim statement As Variant, file As Variant
        Dim sErr As String, iErr As Integer
        With New Access.Application
            With .DBEngine.CreateDatabase(pathDB, dbLangGeneral)
                For Each file In Split(pathSQL, ";")
                    sql = fetchSQL(file)
                    For Each statement In Split(sql, ";" & vbNewLine)
                        If Len(statement) < 5 Then GoTo skpStatement
                        Debug.Print statement
                        On Error Resume Next
                        .Execute statement, dbFailOnError
                        With Err
                            If .Number <> 0 Then
                                iErr = iErr + 1
                                sErr = sErr & vbCrLf & "Error " & .Number & " | " & Replace(.Description, vbCrLf, vbNullString)
                            End If
                        End With
                        On Error GoTo 0
                    Next statement
                Next file
            End With
            .Quit acQuitSaveAll
        End With
        dTime = Now() - starttime
        ' Returnwert
        If sErr = vbNullString Then sErr = "Keine Fehler"
        createDB = "Zeit: " & Now & " | Dauer: " & Format(dTime, "hh:mm:ss") & " | Anzahl Fehler: " & iErr & vbCrLf & sErr
        ' Log
        With ThisWorkbook
            .Saved = True
        End With
    End Function
  • create mail merges from Excel in a Word.Application, using existing and rather large .docm-templates and dynamic SQL-queries that returns the receipents

    Set rst = GetRecordset(ThisWorkbook.Sheets("Parameter").Range("A1:S100"))
    With New Word.Application
        .Visible = False
        While Not rst.EOF
            If rst!Verarbeiten And Not IsNull(rst!Verarbeiten) Then
                Debug.Print rst!Sql
                .Documents.Open rst!inpath & Application.PathSeparator & rst!infile
                stroutfile = fCheckPath(rst!outpath, True) & Application.PathSeparator & rst!outfile
                .Run "quelle_aendern", rst!DataSource, rst!Sql
                .Run MacroName:="TemplateProject.AutoExec.SeriendruckInDokument"
                Application.DisplayAlerts = False
                .ActiveDocument.ExportAsFixedFormat _
                    OutputFileName:=stroutfile _
                    , ExportFormat:=wdExportFormatPDF _
                    , OpenAfterExport:=False _
                    , OptimizeFor:=wdExportOptimizeForPrint _
                    , Range:=wdExportAllDocument _
                    , From:=1, To:=1 _
                    , Item:=wdExportDocumentContent _
                    , IncludeDocProps:=False _
                    , KeepIRM:=True _
                    , CreateBookmarks:=wdExportCreateNoBookmarks _
                    , DocStructureTags:=False _
                    , BitmapMissingFonts:=True _
                    , UseISO19005_1:=False
                Application.DisplayAlerts = True
                For Each doc In .Documents
                    With doc
                        .Saved = True
                        .Close SaveChanges:=wdDoNotSaveChanges
                    End With
                Next doc
            End If
    End With


  • When run on a smaller scale (for example, when querying less records or using less complex templates), both codes do run smoothly.
  • In both cases, when I OK through all the reappearing prompts, the code will eventually finish with the desired results. Therefore, I guess I'm not encountering an error (also it doesn't trigger the error handlers), but rather something like a timeout.

As suggested on other sources, I do wrap my code into Application.DisplayAlerts = False. This, however, seems like a horrible idea, since there might actually be cases where I do need to be alerted.

Apr 5, 2022 in Database by Edureka
• 13,670 points

1 answer to this question.

0 votes
The first step in troubleshooting is to identify the source of the problem.

1. Go to the File menu on the Excel sheet. Select Options from the File menu.

2. A dialogue box called Excel Options will appear. Scroll down to the General section of the Advanced tab. Check Ignore other programmes that use Dynamic Data Exchange over there (DDE).

3. Close Excel and open it again.

The second round of troubleshooting

I attempted to open Excel in safe mode.

The third step in the troubleshooting process is to identify the source of the problem.

All of the add-ons have been removed.
answered Apr 6, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

What is the best library in python to deal with excel files?

XlsxWriter and Xlwings are the best, in ...READ MORE

answered Apr 4, 2022 in Database by Edureka
• 13,670 points
0 votes
0 answers
0 votes
2 answers
0 votes
1 answer

I have to build a school website How can i connect databases to the instance (ec2) and what is the best AMI for my webserver INAWS ???

Hi@Trinu, I don't think your database is depends ...READ MORE

answered May 4, 2020 in Database by MD
• 95,440 points
0 votes
1 answer

How to generate the statement CREATE TABLE for an existing table in postgreSQL?

Generate table creation statement for an existing ...READ MORE

answered Feb 9, 2022 in Database by Neha
• 9,060 points
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
0 votes
1 answer

How to get the "Name Box" name of an Excel cell?

By typing in the Name Box, you ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to use xlrd for writing an excel file

In Python, you can read and write ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP