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)
                                .Clear
                            End If
                        End With
                        On Error GoTo 0
    skpStatement:
                    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
            .Save
        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
            rst.MoveNext
        Wend
    
        .Quit
    End With
    

notes:

  • 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 in Database by Edureka
• 13,640 points
95 views

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 by gaurav
• 13,460 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 in Database by Edureka
• 13,640 points
80 views
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,360 points
286 views
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 in Database by Neha
• 8,560 points
193 views
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 in Database by gaurav
• 13,460 points
44 views
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 in Database by gaurav
• 13,460 points
192 views
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 in Database by gaurav
• 13,460 points
112 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP