title: Pause code execution but let Excel work
body: <p>I am making a macro that I want to do the following things:</p>
  
  <ol>
  <li><p>Open a file.</p></li>
  <li><p>Wait till the file fills with data (this file uses cell formulae to download data from external database, the download takes approx 15 seconds)</p></li>
  <li>After 20 seconds check if everything is downloaded, if no, wait further 10 seconds (up to minute total waiting).</li>
  <li>If everything is downloaded, archive and close the file.</li>
  </ol>
  
  <p>The problem I have is stopping code execution for some time while letting the formulae update themselves in the meantime - I tried Application.Wait after reading at MSDN that:</p>
  
  <blockquote>
    <p>The Wait method suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer while Wait is in effect. However, background processes such as printing and recalculation continue."</p>
  </blockquote>
  
  <p>but it didn't work - Wait stopped both the code and the data download. Is there any way to stop the code for a while but let all other Excel activity continue? I'd like the macro to be able to run completely unattended as it is possible that it will have to be ran in the middle of the night.</p>
  
  <p>Edit: tried Siddarth's answer and it doesn't work for me. The code I tried:</p>
  
  <pre><code>Sub processfile()
  
      Dim bbgwb As Workbook
      Dim filepath As String
  
      filepath = "C:\Test\0900CET.xls"
      Set bbgwb = Workbooks.Open(filepath)
  
      Wait 60
  
      filepath = "C:\Test\Archive\"
      If Len(Dir(filepath)) = 0 Then
          MkDir filepath
      Else
          'Do nothing.
      End If
  
      filepath = "C:\Test\Archive\0900.xls"
  
      bbgwb.Worksheets(1).Range("A1:AZ200").Copy
      bbgwb.Worksheets(1).Range("A1:AZ200").PasteSpecial xlPasteValuesAndNumberFormats
      bbgwb.Worksheets(1).Range("C142").Value = Now
  
      bbgwb.SaveAs Filename:=filepath, FileFormat:=56
      bbgwb.Close
      ThisWorkbook.Close
  
  End Sub
  
  Private Sub Wait(ByVal nSec As Long)
      nSec = nSec + Timer
      While nSec &gt; Timer
          DoEvents
      Wend
  End Sub
  </code></pre>
  
  <p>0900CET.xls has the formulae which, after opening it, autoupdate with data (which I want then store and archive, that's why I add the whole copy/paste special part at the end). I just tested opening 0900CET.xls manually (all formulae updated after 17.3 seconds) and via the code above (not a single cell updated before the file got archived.</p>