Scheduling automated pivot table refresh

[dotsent]

Hi all!

I'm running Excel 2016 in Office 365 package (files not on OneDrive cloud as of now, should it matter). I operate 2 files - a template *.xlsm file where users do modifications and run VBA-s. Although they can enter data, the file is in read-only folder. The other is sort of a *.xlsm database file.

The template file has a simple pivot table which fetches data from a table in the database file. Haven't yet tried to, but even though in read-only folder, I believe the users can refresh the pivot table manually to fetch new data into the template file. As it is read-only, they obviously can't save the refreshed data.

Now this all is fine - however I would still want to have a process where the pivot table in this template is periodically refreshed AND SAVED. I'm thinking once a night.

I'm quite new to VBA and Excel Get & Transform functionalities - could you please advise me on what might be the reasonable way to do this? Is it that I can't bypass a server with write-rights to the designated folder which would then launching the template file at some X hour + run a macro (refresh pivot table + save + close workbook)?

While I have activated macro to be launched on file open, I don't think I can use this here as I don't want this macro to be launched when regular users open the file. Or is there a more elegant way to handle this? Or is VBScript the way to go which would open the Workbook and launch the proper macro externally?

Any feedback is greatly appreciated!

Thanks!

 

[DaveBlakeMAAT]

I believe the workbook would have to be open for the code to run, have you considered setting the pivot table to refresh on the file opening?

This can be achieved by selecting a cell in the pivot table and going to analyse ribbon>Options (Under PT Name) and selecting the Data tab. There is an option to refresh date when opening.