Set schedule for query to update automatically at a specific time in Excel

Check out the link below.

Download the template script and Open it. Ignore the instructions in the dialog box for the moment. Just OK it. In cell B9, type in the path to where the excel executable exists for your OS and version of Excel. The one in the script is of Excel 2013. Close and save the template. You are now set up to use this template to execute code in any workbook.

To run a specific report:

Make a copy of the script and rename it with a descriptive name like "Run Report XYZ"

Open the copy and fill in the path name to the report you want to run (e.g. \\ServerName\Pathanme), the report you want to run (e.g. Report XYZ.xlsm) and the Macro in that report that you want to run. I usually name the macro I want to run, Executive, and it normally calls other macros in the workbook to refresh data, set pivot table filters or whatever.

Then open notepad and copy in the contents of cell B11. This string will be used in the windows task scheduler. You can close and save the script file at this point.

I suggest you do a web search on how to use the windows task scheduler. Here is a good one.
http://www.howtogeek.com/123393/how-...ask-scheduler/

Just remember you’ll want to run a program as an action.

How the system works is that you use the task scheduler to set up a schedule when to run the report. When you get to the part "run a program," copy in the contents of cell B11. When you click to move on, you will get a message about it having an argument. OK this dialog box.

Optional: before finishing the task, check the box to open the Properties. One of the options is to run the task whether the user is logged in or not. If you select this option, you can leave your workstation logged off, but you will have to provide a current password. So when your password changes, you'll have to go back in and reset it.

If you plan on staying logged in and lock your workstation, you do not have to do this. Power failures happen or IT runs an update and computers reboot. You know best about the environment in which you work.

How the system works is that the windows task manager opens the script file. The script file has an on open macro that opens the target report and runs the macro you specify. It also records the result of the run in cell B6.

When you initially open the script to fill in the data, this macro puts up the dialog box telling you what to do and bypasses the rest of the code. After the three lines are filled in, then opening the macro runs the report automatically. If you want to check the status of the last run or for some reason alter the script, open Excel and navigate to the script in Excel – hold down the SHIFT key while opening it and this will disable the macro.

One of the advantages of the two-step (scheduler runs the script, script runs the report) is that the report would otherwise have to be designed to have an on open, run a macro event which makes it more cumbersome to work with manually.

Also the scripts allow you to produce a large number of reports on an “industrial” scale.

There are some caveats: both the script location and the report location must be in a trusted location. Look under File -> Options -> Trust Center.

I have all my scripts in a script directory and all my reports in a subdirectories under a Reports directory.

Also the windows task manager is dumb when it comes to your computer setup. It doesn’t know where Excel lives, for example, which is why you have to tell it that when you set up the template.

The task manager is unaware of drive mappings, so it usually safer to use complete path names. One exception is C: - everybody has one of those and every computer knows where it is.

Other restrictions are explained in the article. If you have any questions on how to use the system, just ask.

http://www.utteraccess.com/wiki/inde...a_Macro_to_Run