Access/Excel VBA - Time delay -

note:

1. refresh tables in excel linked access database

2. tables in excel need refreshed in order e.g test_sheet1, test_sheet2, test_sheet3

3. excel files accessed multiple users

question

in access vba, if excel file in use (read only), how can implement delay in access vba code wait file read/write can continue code (refresh tables , save/close file). please note excel files need refreshed in order.

i did implement error handle time delay, if error number = 1004 delay x. didn't job.

timing delays in vba

function refreshexceltables()   dim excelapp 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 

popup messages (images below)

update

function refreshexceltables()  on error goto error  dim excelapp 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  call pause(5)  resume  end if  set excelapp = nothing   end function    public function pause(intseconds integer)  dim dblstart double  if intseconds > 0  dblstart = timer()  while timer < dblstart + intseconds  loop  end if  end function 

i used use pausing code processing:

public function pause(intseconds integer)      dim dblstart double      if intseconds > 0          dblstart = timer()          while timer < dblstart + intseconds             ' twiddle thumbs         loop      end if end function 

so just: call pause(1) wherever need pause @ , wait second.

works if need delay in full second increments. have more robust 1 more code can used smaller increments if want instead.