Is there a way to open a workbook, refresh everything and close after success.

[Saud_Jilani]

I have 2 files, first one is the Master Inventory and second one is the Partial Inventory. Basically, the second file is a subset of the main file and has a data model relation. I need a way to refresh it periodically (for instance daily). But I need to open the file and click refresh all for that. How can I automate it? Both files are stored on OneDrive.

I have been trying to achieve this for a while, but couldn't. I used VBA, PowerAutomate and even Office scripts. Read multiple articles to achieve this via VBA, but no success. All I get is the workbook gets opened and close without the update. The part of the VBA script to open it and Refresh works, but as soon as I add the Close file command - it doesn't save the updates.

Office Script works and would do it at set time but doesn't refresh external connections as it is not supported in Office Web (whereas interestingly, the trigger to start automatically is only supported by Office Online).

Ideally, it would be best to achieve this on cloud.

 

[severynm]

Hello. Some questions:
1. Is background refresh enabled for the query?
2. Are you saving the workbook before closing?
3. Are you using the SaveChanges argument to Workbook.Close?