CODEWITHSUNDEEP
vbaexcel
Vinod Chelladurai
Vinod Chelladurai

Reputation: 539

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

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 = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "Done"
        .Body = "Done"

        .Send
    End With

    Set OutApp = Nothing
    Set OutMail = Nothing

End Sub

Upvotes: 4

Views: 21066

Answers (3)

Arnaud
Arnaud

Reputation: 1

I had this issue for an other strange reason : in an Excel script I was writting to an other excel workbook. And sometimes, this message appeared. What a mess for debugging such situation because everything is blocked until you kill the target excel file (the one where I was supose to write inside). At the end, I found the problem : a bug of Office 2013 (because this pb doesn't exist in Office 2010) ? I was trying to put a text (but not so big, in reality, with some vbLf inside...) in a standard cell, with a standard width. By changing the size of the column to 100 (at the end, something different from the original standard size), before writing inside the pb disappered! Ex. before writing in the cells :

DocExcel.Sheets(1).Select
DocExcel.Sheets(1).Cells.ColumnWidth = 100
DocExcel.Sheets(1).Cells(1, 1).Select

Strange but it works...

Upvotes: 0

newstockie
newstockie

Reputation: 124

You can use this code before the long running tasks Application.IgnoreRemoteRequests = True

Can put it back to original after the task ends using Application.IgnoreRemoteRequests = False

Upvotes: 1

MrPandav
MrPandav

Reputation: 1871

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

Upvotes: 2

Related Questions