I built a power query in Excel that pulls data from the newest file in a shared folder. It is an "OLE DB Query" with an SQL command type (not sure if that is relevant information but thought I would include it.)
The file is updated with new data automatacically and is saved out manually (unfortunately, not always using the same naming convention) in the shared folder twice per day (around 9:45AM and 4:30PM). I would like to make the query refresh at those times; however, when I go into the Query Properties under "Usage" and then under "Refresh Control" the only options are: "Enable Background Refresh", "Refresh every __ Minutes", "Refresh data when opening the file", "Refresh this connection on Refresh All", and "Enable Fast Data Load". Currently I have enabled Refresh Data when opening the file and Refresh this connection on Refresh all.
Is it possible to make the query automatically refresh the data at those two specific times (9:45AM & 5:00PM) every day either using VBA or another method?
NOTE: I have some experience using VBA code but only very basic knowledge.
yes it is possible.
Requirement:
- excel needs to be opened -> in Workbook_Open event, trigger to open hidden form (it won't be visible for the user, but it will be "working" in the background).
There is an event for the forms called "Timer" -> every set interval of time, procedure is triggered (for example every 10 minutes) -> in this procedure you check if current time (now()) = 5:00 pm -> then execute procedure to update otherwise exit sub (note, dont check specific time but a range for example (4:58 pm < x 5:05 pm))
Link:
https://learn.microsoft.com/en-us/office/vba/api/access.form.timer
Alternatively:
- you need to set windows schedule, to open excel at specific time -> update/check will be triggered on Workbook_Open event