Automatic refresh of data

I assume you use Get & Transform to connect to SQL?

Set Query to refresh on file open. And use task scheduler to open the excel file on set time.
Or run script from PowerShell.

Have a read of link below.
https://www.digitalcitizen.life/adva...task-scheduler

There is an issue with putting code in the Open Workbook event. It will run whenever you open the workbook unless you navigate to it using Excel and then hold down the shift key while opening it. So if you want to look at the report after the data has been run and you do not want to run the data again, you will have to do this.

Unfortunately the task scheduler can only open a file, but it cannot run a macro in the file.

To get around this, I use a two-step process. I create a script, the script uses the Open Workbook event to run a macro in the target workbook. Initially when you open this script, you will be asked to fill in the path name to the target workbook, the name of the target workbook, and the macro in the target workbook that you want to run. When you open the script thereafter it will run the macro automatically unless you use the method of holding down the shift key as described above.

This wiki has more details: http://www.utteraccess.com/wiki/Schedule_a_Macro_to_Run

I generally have a directory for all my scripts and another parallel directory for the target reports. I have the target reports in sub-folders in the parallel directory because I usually also have the documentation about that report in the folder too. It just keeps things neater.

Make a copy of the template script and give it a descriptive name - I usually have the script name mimic the task name.

One of the advantages of the script is that you can copy the contents of cell B11 to notepad. This is the string you need when you set the task scheduler. It tells the task scheduler to run the script file. You need to check the contents of cell B9. This is the full path to the Excel executable on your system. Use a full path name in Cell B2 also.

Now as for running the data. You can use refresh all as indicated above in the target macro. The target macro is in a regular module. I have a word of caution on this. Go to the Data Ribbon and select Queries and Connections. Click on each connection name and go to properties. By default Allow Background Refresh is enabled. I suggest disabling it. By disabling it, the code waits until the query is complete before moving on. This is important if you have code such as refreshing a pivot table that follows the refresh.

That should get you started.

I do have other code that reads a "configuration table" and allows me to run only queries that I have "checked off" in the table. This same code also allows me to rewrite the SQL code on demand. Normally I use it to pass parameters such as dates, but I have also generated a list of models in an IN statement - this IN statement changes weekly depending on what models are "hot." I put the model list in an excel table, use a formula to generate the list, give the list a name, put the name in another excel table that contains the SQL code an all runs automatically. If you are interested in this, I can dig up a sample.