Auto Refresh Power Query at 5:00AM, M-F

[CorrieAnn]

I have a power query ("Query - qry_Riders_Last_5_Years") located on sheet tab "Previous RolledUp Cost Data" which needs to be refreshed only once each workday. I prefer this to happen before everyone gets in and starts their day. This way, they can come in, open the tool and begin working without having to worry about data maintenance.

I know there is a setting in the properties screen, but the highest minute value it allows for is 999. That's just a little more than 16 1/2 hours. So, I need to do this with VB. I cannot use the On Open function and/or query property because it is a shared file that is opened multiple times a day by multiple users. I also am not sure how to handle opening the file and running the script without having an actual person doing it. I think Windows has a scheduler, but I do not know how to use it.

Once the update happens, sheet tab "Dashboard" needs to be auto date/time stamped in cell M5 with the computer ID in M7, assuring users that the data has, in fact, been updated and that the tool is ready to be used.

Can someone assist me with this?

 

[FatBoyClam]

The workbook needs to be open for the query to be refreshed, so your two most obvious options are;

1) have it opened by a windows scheduler task on an always-on, unattended, logged in machine

• Use a workbook_open code module to check that the current machine is the 'robot' machine, and if so, refresh the query and record the machine ID and timestamp in the desired place in the workbook.
• If the 'Robot' machine is also a user's machine, you could augment the test by checking the timestamp recorded, and only updating the query if the timestamp is older than today.

2) have the first user to open it have to wait for it to refresh

• use a workbook_open code module to check the timestamp value, and refresh the query if the timestamp is older than today, then record the time in the timestamp cell and save the workbook.
• have the code skip the refresh if the workbook is readonly and alert the user if the timestamp is older than today.

The problem with 1) is having a 'robot' pc that is always on and you can schedule tasks for - commonly in an enterprise environment the PC will have IT set profile that will shut it down after a period of inactivity. Also, it'll probably periodically reboot automatically after installing software updates, at which point it will no longer be logged in, so will not run the schedule.

Before going into detail about setting a Windows schedule task, I'd recommend engaging with your IT team to discuss the viability of having an always-on, logged-in PC that won't perform unattended reboots.