PDA

View Full Version : Update links with all files closed



Meatball
08-31-2009, 10:58 AM
I have a workbook which contains multiple links. There are 30 columns of links per workbook and near 500 workbooks being linked. Our server was moved to a remote location, causing a slowdown of opening and closing files. With all the links on this Status Sheet it takes 20 minutes to update the links. I was thinking I would create a macro to run a couple of times a day that without opening the Status Sheet would:
1-would run Application.OnTime a couple of times a day, if it can be set up to run more than 1 time per day, I am not sure about that. This only needs to be done on workdays and nights. I turn the computer off on weekends.
2-update all the links without opening any workbooks.
3-Create a copy of the Status Sheet with values only and named by date and time.
I believe this will allow me to see the Status Sheet with only 4 hours of delay which should be enough for me to track what needs tracking.
I think I could get most of this myself but have not been able to find out how to do the update part in a closed workbook.
Any help or alternatives using only Excel would be appreciated.

Benzadeus
09-01-2009, 08:25 AM
Does this help? http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado

Meatball
09-01-2009, 09:16 AM
Benzadeus, Thank you. I looked at the link but I do not think I can do what I wanted, which was to avoid opening any of the files. It just takes to much time and uses to much of the computer resources to do while working. It looks like I will have to work with windows scheduler, if it will work when I am logged off. Otherwise I will have to try to come up with a whole different type of plan.

Benzadeus
09-01-2009, 09:36 AM
Why don't set the calculation to Manual and choose No when opening Satus Sheet and get the question if you want to update links with another workbooks?

You would let the Workbook that contains the Status Sheet opened and would add a routine do
Application.Calculate
on On Time event, as you gave the idea.

Meatball
09-01-2009, 09:48 AM
It sounds like that might work. I will check it out when I have some time. Thank you

sidkc
10-07-2009, 04:03 AM
I have a simlar problem.

I am adding links in a sheet to books which do not yet exist.

How do I stop Excel looking for the 'missing' sheet?