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.
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.