Opening Multiple Linked Workbooks

[CPGUY33]

I have an excel workbook that has linked cells in it to another workbook/file. Now this other workbook/file has some links to a third workbook/file, and so on. I have used INDIRECT.EXT and the Morefunc add-in, but this only works for the first linked worksheet. Is there a way to write a maro that opens, and then closes, all linked workbooks in the sheet just to update the linked cells? If it works correctly than I can open the first workbook, the macro will automatically open the second workbook, and the macro in that workbook will open up the thrid workbook. In these workbooks there are multiple links to multiple worksheets, can the marco check that if a link exists anywhere in the workbook then open it?

 

[GlennUK]

Hi CPGUY33,

I can see why you want to use INDIRECT.EXT now. It makes things a little more difficult than if you had direct links ( that would have been a piece of cake ). I think you'll need a macro that looks at every formula in the open workbook, and if INDIRECT.EXT has been used then extract the filename and open it, and do the same for that one, and so on. You'd need to keep track of which files had been opened so that formulae don't trigger the same sequence of events over and over again.

 

[GlennUK]

Hi seenfresh

I also have the same problem where I have various links to different external workbooks and am trying to solve the problem of making sure that all linked workbooks are open before any adjustments are made, thus eliminated the constant irritation of blowing links. I cannot use name defined ranges because of the large amount of data...

I am very interested to see if this macro will work!

Cheers!

Glenn

If I have w1 that is linked to A5 of w2 which is linked to A5 of w3

If I direct link to w2 and insert and adjust rows to w1 with w2 and w3 closed I will most likely blow my links with name defined referencing.

If I Indirect link from w1 to w2 which gives me the data in w3 "indirectly"

I would still blow the links if both w2&3 were closed if I made adjustments to w1

I guess Indirect referencing just minimizes the potential possibility of blowing any links in which I mean: cells referenced to external referenced cells R1C1 style and R1 style will not follow it's linked source when the books are closed while making inserting and deleting rows

This is pretty much my understanding of direct and indirect referencing please comment if I'm way off base!

Thanks,

Glen,

My understanding is that linking without name defined referencing will have the possibility of blowing its cell reference from the original source when inserting and deleting rows in a w that is externally linked to others. Indirect linking would just minimize the amount of externally linked w's that need to be open: in this I mean if I have 3 w's all linked: with direct linking I have the possibility of blowing all three referenced links using R1C1 and R1 style, with Indirect I would only lose one w link instead of both

This is my understanding please comment if I'm way off base!

Thanks,

I don't see how using INDIRECT improves your situation at all. And I can't comprehend what you are getting at in your explanation of your "understanding" of how linking works, so can't comment on whether you are off base or not.

 

[caroline.vanbommel]

i also have the same sort of problem

mine is a bit simpler i think.
I have one main workbook that is linked to 2 other workbooks(and those 2 others are both linked each to 2 other workbooks
So when i open my analysis (main workbook, used just as a viewer sheet ) , i have to open the other 6 workbooks.
I want to avoid having to do this.

4x Masterdata wookbooks ( refreshed monthly)
2x Analysis and Formulas ( variables that are manually changed eg month)
1x Consolidated workbook of the analysis one.

so is there a way to not have to open all of these workbooks if i just want to view the consolidated workbook

 

[Andrew Poulsom]

Excel only updates links in an open workbook. It doesn't update links in a workbook on disk.

Say Book1 has a link to Book2 which has a link to Book3. To update for changes in Book3 you must update the links in order of dependencies, ie Book2 then Book1. But you don't need to have all 3 workbooks open at the same time; you can open Book2 calculate, save and close it, then open Book1.

Of course the above assumes that you are not using formulas that require the source workbook to be open.