Access/Excel VBA - Time delay

Question

Note:

Refresh tables in Excel that are linked to an Access database
Tables in Excel need to be refreshed in order e.g Test_Sheet1, Test_Sheet2, Test_Sheet3
Excel files are accessed by multiple users

Question

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.

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.

Timing Delays in VBA

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

Popup messages (images below)

enter image description here

Update

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 > 0 Then

dblStart = Timer()

Do While Timer < dblStart + intSeconds

Loop

End If

End Function

something like this? `Application.Wait Now + TimeValue("00:00:01")` — Bigtree, May 11 '15 at 18:00
to be clear: Your code is in Access, and you're trying to update an Excel spreadsheet that someone else may have open? You need to wait until they've closed it in order to refresh it? — FreeMan, May 11 '15 at 18:02
Correct FreeMan I just wanted to see if there was a way to delay the code once they are finished ,resume the code ,refresh the tables and save the workbook — Elixir, May 11 '15 at 18:05
Bigtree, unfortunately Application.Wait doesn't work in access vba — Elixir, May 11 '15 at 18:06
Maybe give the whole thing a big re-think: Have your Excel workbook connect directly to the Access database so that every time it's opened, it refreshes the data (optionally, if it's a slow process, have the user click a Big Button™ to get a refresh), then store the original workbook as a template (.XLTM), that way each user gets his own copy. Don't know enough about your situation to know if that would work, just throwing out an idea. — FreeMan, May 11 '15 at 18:09
Wise words FreeMan much appreciated. This approach would be great however I do need to refresh the tables in order (1,2,3) due to data connections. I'll try and see what I can do here — Elixir, May 11 '15 at 18:21
The Excel could still call the access and run the macro there... I mean the users are probably not browsing it if you're putting your report in Excel.. — user3819867, May 11 '15 at 18:27
See [link](http://stackoverflow.com/q/20403424/3819867) for reference. — user3819867, May 11 '15 at 18:34
user3819867 - Thanks for link , I can update the tables in excel manually but i wanted to see if I could do this via access (prefer the one button approach) — Elixir, May 11 '15 at 18:39

score 1 · Accepted Answer · answered May 11 '15 at 18:12

1

I used to use this for pausing code processing:

Public Function Pause(intSeconds As Integer)

    Dim dblStart As Double

    If intSeconds > 0 Then

        dblStart = Timer()

        Do While Timer < dblStart + intSeconds
            ' Twiddle thumbs
        Loop

    End If
End Function

So you would just: Call Pause(1) wherever you need the pause at and it will wait for a second.

Works well if you only need to delay in full second increments. I have another more robust one with more code that can be used for much smaller increments if you want it instead.

answered May 11 '15 at 18:12

Newd

2,174
2
17
31

Thanks Newd I did something similar but incorporated this with an error handle however i'll give this a go – Elixir May 11 '15 at 18:23
Newd - I used your code with a error handle e.g if error = 1004 call Pause , This worked fine expect I keep getting that dialog box (Image in question) telling me to replace file , I just keep clicking no till the file is free (Read/Write mode). So it works but I have to keep pressing no on message box – Elixir May 13 '15 at 17:57
Can you post your updated code into the body of your question? – Newd May 14 '15 at 12:28

observer99 · Answer 2 · 2020-06-19T13:29:36.607

'This code pauses running code using the timer function, making special provisions for midnight (when the timer resets to 0). Implemented in MS Access

 Public Sub Pause(NumberOfSeconds As Double)
On Error GoTo error_goto

Dim PauseInterval As Variant   'Pause interval is the wait time
Dim StartTime As Variant       'wait start time
Dim ElapsedInterval As Variant  'time elapsed from start time to now
Dim preMidnightInterval As Variant   'time interval from start time to midnight
Dim endTime As Variant

'initializing variables
PauseInterval = NumberOfSeconds
StartTime = Timer
ElapsedInerval = 0
preMidnightInterval = 0
endTime = StartTime + PauseInterval

Do While ElapsedInterval < PauseInterval
ElapsedInterval = Timer - StartTime + preMidnightInterval
'During the day premidnightInterval =0
'shortly after midnight is passed timer is almost 0 and preMidnightInterval becomes non zero
'detecting midnight switch
'the instant midnight is passed ElapsedInterval = 0 - starttime + 0
    If ElapsedInterval < 0 Then
    preMidnightInterval = 86400 - StartTime 'interval segment before midnight
    StartTime = 0       'reset start time to midnight
    End If
DoEvents

Loop
'Debug.Print "starttime " & StartTime & "elapsed interval " & ElapsedInterval & " timer:" & Timer & "endtime:" & endTime
Exit_GoTo:
'On Error GoTo 0
Exit Sub

error_goto:
Debug.Print Err.Number, Err.Description, er1
GoTo Exit_GoTo

End Sub

Access/Excel VBA - Time delay

2 Answers2

Linked