Excel is waiting for another application to complete an OLE action

Question

Before you go for the obvious: Application.DisplayAlerts = False has not solved my problem.

I have written a VBA procedure (initiated in Excel 2010) which loops around an array containing different Excel files. The loop opens the file, refreshes the data, saves and closes the file for each item in the array. I have written an error catch sub routine so I log which excel files have failed to open/refresh/save etc so a user can manually check them.

Some files are quite large and involve a large amount of data moving across the network; sometimes I get a dialog box with: Excel is waiting for another application to complete an OLE action.

I could use Application.DisplayAlerts = False to disable the message but this would presumably disable all alerts so I couldn't catch the errors?

Further I have tested using the line and it doesn't stop the dialog box pop-up. If I press enter it carries on but will likely pop-up again a few minutes later.

Is there a way to stop is message specifically without stopping other alerts?

NB. My process has a control instance of Excel which runs the VBA and opens the workbooks to be refreshed in a separate instance.

Thanks for your help

An extract of my code is below which contains the refresh elements

Sub Refresh_BoardPivots_Standard()
'    On Error GoTo Errorhandler

Dim i
Dim errorText As String
Dim x
Dim objXL As Excel.Application
Set objXL = CreateObject("Excel.Application")

GetPivotsToRefresh ' populate array from SQL
For Each i In StandardBoardPiv
DoEvents
'If File_Exists(i) Then
    If isFileOpen(i) = True Then
    errorText = i
    Failed(failedIndex) = errorText
    failedIndex = failedIndex + 1
    Else
    objXL.Visible = True 'False
     objXL.Workbooks.Open FileName:=i
        If objXL.ActiveWorkbook.ReadOnly = False Then
        BackgroundQuery = False
        Application.DisplayAlerts = False
        objXL.ActiveWorkbook.RefreshAll
        objXL.Application.CalculateFull
        objXL.Application.DisplayAlerts = False
        objXL.ActiveWorkbook.Save
        objXL.Application.DisplayAlerts = True
        objXL.Quit
        Else
        errorText = i
        Failed(failedIndex) = errorText
        failedIndex = failedIndex + 1
        objXL.Application.DisplayAlerts = False
        objXL.Quit
        Application.DisplayAlerts = True
        End If
    End If
'        Else
'        errorText = i
'        Failed(failedIndex) = errorText
'        failedIndex = failedIndex + 1
'    End If
DoEvents
If Ref = False Then
Exit For
End If

Next i

Exit Sub

'Errorhandler:
'
'errorText = i
'Failed(failedIndex) = errorText
'failedIndex = failedIndex + 1

'Resume Next
End Sub

Answer 1

"Waiting for another application to complete an OLE action" isn't an alert message you can just turn off and forget, sometimes the macro will be able to continue on after, but in my experience if you are getting that error its only a matter of time until the problem crashes/freezes your whole macro so it should definitely be troubleshot and corrected.

I only get that error when I am using additional Microsoft Office Applications (other than the Excel that is running the code) as objects and one of them has an error- the Excel running the code doesn't know that an error occurred in one of the other applications so it waits and waits and waits and eventually you get the "Waiting for another application to complete an OLE action" message...

So to troubleshoot this sort of problem you got to look for the places you use other MSO apps... In your example, you have an additional instance of Excel and you are pulling data from Access, so its most likely one of those two that is causing the problems...

Below is how I would re-write this code, being more careful with where the code interacts with the other MSO apps, explicitly controlling what is happening in them.. The only piece I couldn't really do much is GetPivotsToRefresh because I cant see what exactly youre doing here, but in my code I just assumed it returned an array with a list of the excel files you want to update. See code below:

Sub Refresh_BoardPivots_Standard()
Dim pivotWB As Workbook
Dim fileList() As Variant
Dim fileCounter As Long

Application.DisplayAlerts = False
fileList = GetPivotsToRefresh 'populate array from SQL
For fileCounter = 1 To UBound(fileList, 1)
    Set pivotWB = Workbooks.Open(fileList(fileCounter, 1), False, False)
    If pivotWB.ReadOnly = False Then
        Call refreshPivotTables(pivotWB)
        pivotWB.Close (True)
    Else
    '... Error handler ...
        pivotWB.Close (False)
    End If
Next
End Sub
Public Sub refreshPivotTables(targetWB As Workbook)
Dim wsCounter As Long
Dim ptCounter As Long
For wsCounter = 1 To targetWB.Sheets.Count
    With targetWB.Sheets(wsCounter)
        If .PivotTables.Count > 0 Then
            For ptCounter = 1 To .PivotTables.Count
                .PivotTables(ptCounter).RefreshDataSourceValues
            Next
            .Calculate
        End If
    End With
Next
End Sub

So I created my own 'refreshPivotTables' but you could have embedded that into the master sub, I just thought the loops and loop counters might get a little messy at that point...

Hope this helps, TheSilkCode