External data updating macro

[mikeyfear]

Good evening all. When I have needed help on excel in the past I have always asked a friend who posts my problems up on here, and reports them back to me. Unfortunately he is away travelling at the moment, but I have taken the opportunity to join myself, and hope that your excellent assistance continues in his absense! Anyway enough brown nosing....

I realise this is probably a very simple bit of code... but I have so far been unsucessful in perfecting it. I have a very complex financial model with ~100 excel sheets and in total ~400 external links between the files. When I want to refresh the main presentation page of the model, (unless im missing something) I need to update the external links in all the sheets that feed into the next level up. A kind of imaginary pyramid of links updating.

The way it is being done at the moment is by manually opening each file, clicking on update external links, (waiting... and waiting).. and then closing it and starting on the next file. Because of the pyramid system of the model, you have to update them in a fairly specific order, i.e. level to level to get the data you need it. Yes, I know this is an awful system to have in place, which is what I've joined the company to sort out... currently takes in the region of 3 hours to do manually!!

In the mean time, I figured that if I could generate a macro to update open and update the external links in each file from an ordered list kept in a worksheet, then it can open the sheets up in the correct order, force the update of external links and then close them one by one. This may still be a lengthy process as the files are very large, but I'm thinking of perhaps leaving it running at the end of play on an evening, so we're fired up with current data the following morning. I've managed to make a macro that opens them all, which just crashes my machine (genious me!), but have had no luck in one that'll do it'll work through the list file by file, opening it, forcing the links to refresh, closing it, and then moving on to the next link.

Does anyone have some pointers on the code required? Your help as always would be much appreciated

[rylo]

Hi

Say your files are in the range B1:B100, and include the full pathname then something like

Please Login or Register  to view this content.

would open each workbook in order, allow the links to the output file to update, then close without saving.

HTH

rylo

[mikeyfear]

Many thanks this worked nicely.

Rather than post up another thread, and since this is still to do with external links.. this is another problems I'm having, I wonder if anyone could help.

Some lazy so and so, has got fed up with linking to one of our data files after the 3rd period of the year. I.e. sheets 1, 2 and 3 of the workbook are externally linked to the source data from ../accounts/.. p1, p2, p3 workbooks. Sheets 4 to 12 have just had the data cut and pasted in and so aren't dynamic and dont update. This therefore produces incorrect totals in the model when source data is revised.

Since everything on the sheets I'm linking to is in the same place.. I want to be able to cut and paste the whole of sheet1 (i.e. all cells) which are for example (linked to the ../accounts/.. p1 sheet), paste that into my sheet 4 (currently just the data and not externally linked), then just replace all the "P1.xls" references to "p4.xls". Simple enough I thought.

However there are 7000 references to the external sheet, and Excel seems to want me to browse for the external file, for each reference! I don't fancy that!


Is there a simpler way? I tried inserting a ' infront of all the forumulas (i.e. replace = with '=)to turn them into text, and then replace the p1.xls with p4.xls, which worked, but it wont then let me replace the '= with = to turn it back into an external reference... so that was no good.

Surely there must be a better way?

[rylo]

Hi

Have both the P1.xls and P4.xls open (as long as you can do that), then do a find replace from P1.xls to P4.xls. This should change all the links.

If it keeps asking to browse for the external file, then it isn't in the path that the workbook is currently using. If you go File, Open, then change to the path that contains the P4.xls workbook, and if P1.xls doesn't have the same path, then you will have to include the path in the replace statement.

HTH

rylo