Opening Multiple Linked Workbooks

CPGUY33

New Member
Joined
Sep 27, 2006
Messages
9
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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

CPGUY33

New Member
Joined
Sep 27, 2006
Messages
9
If i can get this macro to work then I will use the regular INDIRECT instead of INDIRECT.EXT. I used it because i did not want to have the linked workbook open in order to update the cells, but I have found out that it cannot update links within linked workbooks. So i am trying to create a marco that can check if links are active and then open and close that workbook to update the linked cells? And so on in the linked workbook since it also has links in it.
Unless that is another way?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
What I meant was, what is the formula that you are using? I want to see what kind of operation you are doing, that you need to use INDIRECT.EXT, instead of linking directly.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141

ADVERTISEMENT

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!
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
Also isn't direct linking basically the same thing as INDIRECT if a link is blow anywhere in the source than either way #Ref will exist right?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

Hi seenfresh,

I'm sorry but I don't understand ... "if a link is blow anywhere"?

Direct linking isn't the same as using INDIRECT ... the clue is in the function name "INDIRECT"!
 

CPGUY33

New Member
Joined
Sep 27, 2006
Messages
9
The following formula is what I am using. There can possibly be 42 different links in this workbook and the same number of links in the linked workbook, and so on (not typical to have 42 links but possible).

=IF(B4="Y",INDIRECT.EXT("'["&C4&".xls]Sheet1'!M49"),0)

I am using "INDIRECT" because I am asking the user to input the file name and "INDIRECT.EXT" because i do not want to have all of the 42 or so workbooks open.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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,
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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,
 

Forum statistics

Threads
1,137,366
Messages
5,681,068
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top