Access/Excel VBA - Time delay

1
votes

Note:

  1. Refresh tables in Excel that are linked to an Access database

  2. Tables in Excel need to be refreshed in order e.g Test_Sheet1, Test_Sheet2, Test_Sheet3

  3. 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
2
excelvbams-access
something like this? Application.Wait Now + TimeValue("00:00:01") – Bigtree
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
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
Bigtree, unfortunately Application.Wait doesn't work in access vba – Elixir
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

2 Answers

1
votes

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.

0
votes

'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