Excel expects another application to perform OLE action

Question

Before moving on to the obvious: Application.DisplayAlerts = False did not solve my problem.

I wrote a VBA procedure (started in Excel 2010) that moves around an array containing different Excel files. The loop opens the file, updates the data, saves and closes the file for each element of the array. I wrote a catch catch routine, so I register which excel files could not be opened / updated / saved, etc., so that the user can manually check them.

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

I could use Application.DisplayAlerts = False to disable the message, but it will probably disable all warnings so that I cannot catch the errors?

Next, I checked using this line, and does not stop the popup dialog box. If I press the enter button, it will continue, but most likely will appear again in a few minutes.

Is there any way to stop this message without stopping other alerts?

NB. My process has an Excel Management Instance that launches VBA and opens workbooks that will be updated in a separate instance.

thanks for the help

Below is a snippet of my code that contains update items

 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 the OLE action" is not a warning message that you can simply turn off and forget, sometimes the macro can continue to work, but, in my experience, if you get this error, only a matter of time until the problem crashes / your entire macro freezes, so it should definitely be fixed and fixed.

I get this error only if I use additional Microsoft Office applications (other than Excel, which use code) as objects, and one of them has an error: Excel executing the code does not know that an error has occurred in one of other applications, so it waits and waits and waits, and in the end you will receive the message "Waiting for another application to complete the OLE action" ...

So, to fix this problem, you need to look for places where you use other MSO applications ... In your example, you have an extra instance of Excel and you are extracting data from Access, so its most likely one of the two that cause Problems...

Below I will talk about how I would rewrite this code, being more careful about where the code interacts with other MSO applications, clearly controlling what happens in them. The only thing I really could not do was GetPivotsToRefresh because I cannot understand what exactly you are doing here, but in my code I just assumed that it returned an array with a list of excel files that you want to update. See the following code:

 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 embed them in the main sub, I just thought that the loops and loop counts might get a little confused at this point ...

Hope this helps, TheSilkCode