[Edit:] Fixed code tags [/Edit]
I set up a timer (code adapted from various sources). It calls one sub, which contains the line ThisWorkbook.RefreshAll If I run the sub RefreshData by hitting F5 from within it, it works fine. If I call the sub from within the Timer sub, I get Run-time Error 50290
The data includes various queries to an SQL server database.
The code:
Tried adding DoEvents after, no go. Same error.
Sub Timer()
Dim TimeOut As Long
'Set Timeout in minutes
TimeOut = 5
If blnTimer Then
lngTimerID = KillTimer(0, lngTimerID)
If lngTimerID = 0 Then
MsgBox "Error: Timer Not Stopped"
Exit Sub
End If
Debug.Print "blnTimer = False"
blnTimer = False
Else
lngTimerID = SetTimer(0, 0, TimeSerial(0, TimeOut, 0), AddressOf RefreshData)
If lngTimerID = 0 Then
MsgBox "Error: Timer Not Generated"
Exit Sub
End If
Debug.Print "blnTimer = True"
blnTimer = True
End If
Debug.Print "Timer Complete at " & Time
End Sub
Sub RefreshData()
'Refresh all data connections
ActiveWorkbook.RefreshAll
'Complete all refresh events before moving on
DoEvents
Debug.Print "Data Refreshed at " & Time
End Sub
Expected result is every 5 minutes the sub RefreshData will be called, which will run the command ThisWorkbook.RefreshAll and update all external data connections.
[Edit:] Update - I just tried doing an Application.CalculateFullRebuild (as per here) just above the RefreshAll, and the same error code appears on the CalculateFullRebuild line. The plot thickens...
[Edit 2] I will post my full solution, because I then limited it to our office hours and that might be useful for someone who finds this post as well. Credit to @EvR for the Application.OnTime help! NOTE: The code below must be in ThisWorkbook and the module you want to run must either be in Module1 or you must change Module1 to where your code is - and of course change the name of the Sub from RefreshData to your sub, both in the start timer and end timer subs below...
[Edit3]: I forgot to include the Public variable declaration for MyTime - if you don't use it as a public variable (i.e. outside any subroutine) then the Cancel routine (ThisWorkbook_BeforeClose) won't work and you'll get an error each time you close the workbook: it needs the exact MyTime value to cancel the timer.
[Edit4]: Should be if timer >= officecloses - otherwise it will set Seconds = 0 when the hour is 17:00... And it won't run again until the workbook opens again manually! Below code updated.
[Edit5]: Seconds needs to be Long type because when I do the sum for overnight, there's not enough memory in an integer for the large number of seconds needed! Code updated below.
[Edit6]: I just discovered that you can't add 23 hours to the current time (makes sense when you think about it - date drops back to Excel's first date). I needed to add DateAdd("d", 1, MyTime) and change my initial setting of MyTime to use Now instead of Time (Now includes both time and date). Yes I have manually opened it each morning since this to find the memory error, and ok'd, closed and opened manually... Until today. Today is a new day!! :D Corrected code below.
Public Dim MyTime As Date
Sub RefreshOnTime()
Dim Delay As Integer
Dim OfficeOpens As Integer
Dim OfficeCloses As Integer
Dim Overnight As Integer
Dim DayAdvance As Integer
'Delay in seconds
Delay = 240
'hour of opening
OfficeOpens = 7
'hour of closing (24hr clock)
OfficeCloses = 17
'If in working hours
If Hour(Time) >= OfficeOpens And Hour(Time) < OfficeCloses Then
Overnight = 0
DayAdvance = 0
'If in the morning (e.g. auto open after scheduled reboot at 3am)
ElseIf Hour(Time) < OfficeOpens Then
Overnight = (OfficeOpens - Hour(Time))
DayAdvance = 0
'If after 5pm add 1 to day
'Add morning hours
ElseIf Hour(Time) >= OfficeCloses Then
Overnight = (OfficeOpens - Hour(Time))
DayAdvance = 1
End If
Debug.Print "Hours = " & Overnight
'Add Seconds to current time
MyTime = DateAdd("s", Delay, Now)
Debug.Print "MyTime after adding Seconds = " & MyTime
'Add DayAdvance to MyTime
MyTime = DateAdd("d", DayAdvance, MyTime)
Debug.Print "MyTime after adding DayAdvance = " & MyTime
'Add Overnight to MyTime
MyTime = DateAdd("h", Overnight, MyTime)
Debug.Print "RefreshData will run at " & MyTime
'REPLACE MODULE1 with the right module
'REPLACE RefreshData with the name of your sub
Application.OnTime MyTime, "Module1.RefreshData"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'REPLACE MODULE1 with the right module
'REPLACE RefreshData with the name of your sub
Application.OnTime MyTime, "Thisworkbook.RefreshData", , False
End Sub
Private Sub Workbook_Open()
'Just in case you need to debug
'Uncomment these 3 lines and click "No" on workbook open
'Dim Ans As Variant
'Ans = MsgBox("Do you want to run RefreshOnTime?", vbYesNo, "Yes/No")
'If Ans = vbYes Then RefreshOnTime
RefreshOnTime
End Sub
ActiveWorkbookis not recommended. This is because you could have a workbook open at the time your code is run which is not the workbook you want to reference. Qualify your workbook – Zac