auto open & update links in 20 files at set times

[krazyderek]

So i have some files setup as a make shift database for now;

20 Excel Data Entry Files -> Linked -> to <b>20 Excel Files (calculations)</b> -> Query -> to ~100 Excel files that import the calcuated values in the second step and then works with them further

I'd like to create a script so i can have excel running on a server, and let's say, every hour, or maybe every night, it opens the 20 excel files in the middle of this process, updates the linked data from the Data Entry Files, performs all the calculations, then they close. I'm fine with scheduling all 20, so file1 opens at 12:01, file2 at 12:03 etc.
Ideally i'd like to have some type of autoskip function incase the file is already open, and i'd like to be able to also run this operation manually at any time.

Basically i want to automate the middle step in this chain, while still keeping the calculations in their own files.

 

[Saagar]

This can be done. Not sure if you have Excel VBA experience. Assuming you do, here is an approach:
1. Set up your file to auto-update linked data upon opening without prompting the user.
2. You will find "timer" code if you google.
3. You will find code to open and save file if you google.

You will need to integrate the code and make modifications to fit your needs.