title: Access/Excel VBA - Time delay
body: <p>Note:</p>
  
  <ol>
  <li><p>Refresh tables in Excel that are linked to an Access database</p></li>
  <li><p>Tables in Excel need to be refreshed in order e.g Test_Sheet1, Test_Sheet2, Test_Sheet3</p></li>
  <li><p>Excel files are accessed by multiple users</p></li>
  </ol>
  
  <p>Question </p>
  
  <p>In Access vba, If an excel file is in use (Read only), How can I implement a delay in the Access vba code to wait for the file to be Read/write so that it can continue with the code (refresh tables , save/close file).       Please note that The excel files do need to be refreshed in order.</p>
  
  <p>I did Implement a Error handle with time delay, so if error number = 1004 then delay by X.
  This didn't really do the Job. </p>
  
  <p><a href="https://stackoverflow.com/questions/6960434/timing-delays-in-vba">Timing Delays in VBA</a></p>
  
  <pre><code>Function RefreshExcelTables()
  
  
  Dim ExcelApp As Object
  Set ExcelApp = CreateObject("Excel.Application")
  
  ExcelApp.workbooks.Open "c:\test\Test_Sheet1.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close
  
  
  ExcelApp.workbooks.Open "c:\test\Test_Sheet2.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close
  
  
  ExcelApp.workbooks.Open "c:\test\Test_Sheet3.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close
  
  
  
  Set ExcelApp = Nothing
  
  
  End Function
  </code></pre>
  
  <p>Popup messages (images below)</p>
  
  <p><img src="https://i.stack.imgur.com/FcFwM.png" alt="enter image description here"></p>
  
  <p>Update</p>
  
  <pre><code>Function RefreshExcelTables()
  
  On Error GoTo Error
  
  Dim ExcelApp As Object
  Set ExcelApp = CreateObject("Excel.Application")
  
  ExcelApp.workbooks.Open "c:\test\Test_Sheet1.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close
  
  
  ExcelApp.workbooks.Open "c:\test\Test_Sheet2.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close
  
  
  ExcelApp.workbooks.Open "c:\test\Test_Sheet3.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close
  
  Error:
  
  If Err.Number = 1004 Then
  
  call pause(5)
  
  Resume
  
  End If
  
  Set ExcelApp = Nothing
  
  
  End Function
  
  
  
  Public Function Pause(intSeconds As Integer)
  
  Dim dblStart As Double
  
  If intSeconds &gt; 0 Then
  
  dblStart = Timer()
  
  Do While Timer &lt; dblStart + intSeconds
  
  Loop
  
  End If
  
  End Function
  </code></pre>