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

Question

I am running a macro through a vb script that imports more than 1000 records from database and copies them into an excel sheet and then send an email saying that the report is ready. When i import less records(100 or 200) it is running fine. But when I import the entire records(more than 1000), I get a window message "Microsoft Excel is waiting for another application to complete an OLE action " even though the program is running fine .

Is there any way to hide this message. Also, If i hide this message, will the program continues to run? Below is my code:

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set rst = New ADODB.Recordset
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    cn.Open ("User ID=flt" & _
                ";Password=flts1hp" & _
                ";Data Source=SIH_PROD" & _
                ";Provider=MSDAORA.1" & _
                ";PLSQLRSet=0")

    Set oxcel = New Excel.Application
    Set wbk = oxcel.Workbooks.Add()

    With oxcel
        .ActiveSheet.Name = "Report"
        strFileName = "C:\Users\extract.xlsx"

        'headings
        For i = 0 To rs.Fields.Count - 1
            .Cells(row, col) = rs.Fields(i).Name
            .Rows(row).RowHeight = 45
            .Cells(row, col).WrapText = True
            col = col + 1
        Next

        .Range("A2").Select
        .Selection.CopyFromRecordset rs

        With wbk
            .Application.DisplayAlerts = False
            On Error GoTo Error_Message

            .SaveAs (strFileName), AccessMode:=xlExclusive, _
            ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges

            .Close
        End With
        .Quit
    End With

    With OutMail
        .To = "vinod.chelladurai@abc.com"
        .CC = ""
        .BCC = ""
        .Subject = "Done"
        .Body = "Done"

        .Send
    End With

    Set OutApp = Nothing
    Set OutMail = Nothing

End Sub

You missed an _ after .SaveAs (strFileName), AccessMode:=xlExclusive, to continue on the next line. I added it. — simpLE MAn
Maybe you could put one or two DoEvents in your code like each time you finish a step in your loop. The loop I suppose you are using to call the sub you pasted here. — simpLE MAn
@simpLEMAn : Doevents can be used only when the ost appilcation is visual basic. But my code is purely excel vba... — Vinod Chelladurai
That is not true, DoEvents works in VBA, I use it myself. Also, if you read the very first line of the provided link it is written "Visual Basic for Applications Reference". DoEvents is not going to solve all your problems though it could help unfreeze (i.e. when the bar is showing "Not Responding...") or other things to help clear the tasks queue for the CPU. — simpLE MAn
So..how i can use doevents in my code above?.. Any suggestions please — Vinod Chelladurai

MrPandav MrPandav · Accepted Answer · 2014-03-25T12:04:46

by reading your Question(withouth getting into your code)

Yes - Alert message can be Diabled using

`Application.DisplayAlerts = False`

but that won't solve the problem ,

your programme will still not execute.Since there will be some deadlocks in function calls, (I have had this problem once )

Try debuggin with breakpoint and narrow it down to the erroneous function call

Hope this will help

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

3 Answers