Power Query/ Excel - automated query refresh

Question

I have introduced several Excel files in the company, which use Power Query to consolidate different Excel files and prepare them accordingly. The structure is as follows:

1st file: - Query from Access - Querys from 3 Excel files

2.-5.Datei: Various Excel files.

Some of these files are updated daily with new records. That means I have to daily in the excel files with the queries and these update.

Is it possible to outsource this somehow? Sharepoint, server?

What ideas do you have? Except a VBA macro which opens and refreshes the data accordingly?

Best regards

Answer 1

If you have one file with Power Query that get data from multiple files with daily refreshed data without any Power Query in them then:
In file with Power Query when you add result of Power Query to sheet you will get connection. You can customize this connection to refresh on workbook opening or every N minutes. Is this is what you want ?

Here: http://www.excel2013.info/power-query/automatic-update/ you can see in "Refresh the connection" section how to customize connection.

Refresh the connection
Because your data is in a table, your table is link to your source of data. When you will receive a new csv file with the same name, in the same folder, you just have to refresh the query to update your workbook or you can refresh automatically your table when you open your workbook. To do that, you have go to the menu Data > Connections
In the dialog box Workbook connections, you select one of the connections of your workbooks (here there is only one connection) and you click on Properties …
Select the option Refresh data when opening the file
Don’t forget to save and close your file.

If you have multiple files with Power Query and one file get data from another files results of Power Query then you can schedule some code to refresh connections. You can create Console App (C# language), add some library to work with Excel, get target files, open its workbook, refresh connections. Like here: https://social.msdn.microsoft.com/Forums/vstudio/en-US/b288098d-d4e8-4845-ae3f-38ad235e22aa/how-to-execute-a-quotrefresh-allquot-for-excel-programmatically-in-c?forum=csharpgeneral
Open Excel File, Refresh Query and Save C#
This way multiple files with Power Query will have always updated results of Power Query. And your one main file with Power Query that get data from results of Power Query from this multiple files will have always updated data.