Problem with wait/sleep

I have tried using this method (http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx) but it pauses the Refresh all command

Is this what your looking for?

Application.Wait Now + TimeValue("00:00:05")

The code will wait for 5 seconds.

HTH,
mccreaso

Originally Posted by mccreaso

Is this what your looking for?

Application.Wait Now + TimeValue("00:00:05")

The code will wait for 5 seconds.

HTH,
mccreaso

I have tried that and it doesn't work since the Refresh command gets paused too What I need is something that lets the Refresh command to run whilst pausing the rest of the macro.

Split it into 2 macros and use the Application.OnTime function:

Macro1

Sub Test()

    ChDir "C:\Statistiques\Traitement\Excel"
    Workbooks.Open Filename:="C:\Statistiques\Traitement\Excel\stats mails.xlsx"
    ActiveWorkbook.RefreshAll
    With ActiveWorkbook.PublishObjects("stats mails_610")
        .HtmlType = xlHtmlStatic
        .Publish (False)
        .AutoRepublish = False
    End With
Application.OnTime Now + TimeValue("00:00:05"), "saveDoc" 
' Run save macro after 5 seconds

End Sub

Macro 2

Sub saveDoc()
    ChDir "C:\Statistiques\Statistiques"
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWorkbook.Save
End Sub

Havent tested it, but hope it helps,

mccreaso

worked like a charm, thanks a lot !

Glad it helped !
mccreaso