PDA

View Full Version : Multiple WorkBooks Question



lovespar
12-19-2007, 12:04 PM
I am trying to update a Central workbook with data from 5 different WB's
This seems to be a common occurance here so I have been reading through the threads for some advice on how to create the code to do this.
I have the cells linked within my Macro but the cells change on a monthly basis. Thus I think I will have to repeat my Macro 12 times, referencing different cells each month. Then I can have the user pick the month she wants to Update.
It seems the codes run much faster if the source WB's are open.
Why is this? Should I open each source WB in the code before the transfer?
Thanks

unmarkedhelicopter
12-19-2007, 12:21 PM
The problem is that if you have 5 wb's all updating 1 central wb it makes sense to have the code in the central wb. Then the problem is that when you open that it will immediately try to update all links. and will do so before you get a chance to open the other 5. So good idea but I don't think you should mess with it and this way there is no code.

lovespar
12-19-2007, 12:32 PM
Alas, they are using a templete and the girl operating the update is currently changing all the links by hand every month. It takes her @ 3 hrs to do this and is very tedious. That is why I am trying to code it so she just picks a month and runs the macro. The data is non consistant throughout the spreadsheets, some horizontal, some vertical by month so cell by cell is how I have it set up. If I leave the spreadsheet closed, it takes 4 minutes to run about 30 lines, if I open the spreadhseet up, it takes seconds. That is what I am seeing on our system. Is this normal?

Bob Phillips
12-19-2007, 01:39 PM
Instead of linking them, you could just read them using ADO. It won't be as quick as getting the data when all 5 are open, but it is simple.

Presumably the 12 month of data may be separated but all structured, so you could apply some rule to get at the appropriate month?