No update with more than 3 workbook chain

[Malic]

I know this has been mentioned, but I can't find my exact situation in the
previous few days posts.

I have 5 excel documents. DocA.xls, DocB.xls, DocC.xls, DocD.xls, DocE.xls.

My accounting department has a very elaborate reporting/calculating string
of documents growing. They have over 100 documents all linked to each other
in the same directory.

DocA.xls is modified and it has a link to DocB.xls. DocB.xls uses
information from a linked field in DocA.xls to calculate a new field.
DocC.xls then uses this field to calcualte a field in DocD.xls. DocE.xls
then looks at DocD.xls to make the final calculation.

Problem:
Users report that if they make a change in DocA.xls and save, then open
DocE.xls that the calculation hasn't been changed by the modification just
made to DocA.xls. However, if they go and open all the other files
(DocB.xls, DocC.xls, DocD.xls) that DocE.xls will then have the correct
information.

Can anyone help me with this problem. I have never made a macro so if a
macro is required can you please give me a detailed explaination or tell me
where I can find it I also have found information on "Update Links" and
Tools - Options - Calculation tab, check to make sure "Update remote
reference is checked". I looks like these are checked.

Thank you.

 

[Alan]

DocA.xls is modified and it has a link to DocB.xls. DocB.xls uses
information from a linked field in DocA.xls to calculate a new
field.

DocC.xls then uses this field to calcualte a field in DocD.xls.
DocE.xls then looks at DocD.xls to make the final calculation.

Problem:

Users report that if they make a change in DocA.xls and save, then
open DocE.xls that the calculation hasn't been changed by the
modification just made to DocA.xls. However, if they go and open
all the other files (DocB.xls, DocC.xls, DocD.xls) that DocE.xls
will then have the correct information.

Hi Malic,

If they want the change in DocA.xls to flow right through to DocE.xls
then they have two options really (withouth straying from Excel):

1) Open all of the files from the one that made the change, through to
the final destination and everything in between simultaneously.

2) Open each of the files, in order, one at a time, let it update from
the predecessor, and then save and close it. Continue this through
the chain until you get to the destination.

3) Redesign the whole thing to eliminate links or at least reduce the
number of steps.


Okay - that was three, what a bargain!

Alan.

 

[Malic]

I was afraid of that.

I was thinking ther emight be a macro or some VB I could right to open all
linked files and close silently. Then in each file, docb, docc, docd, I
would have the same macro to open all linked files. So when I open one file
it auto opens all files and closes them without the user seeing it.

Appreciate your help.

 

[amby]

maybe you could save the workspace to open all the workbooks together.

 

[Alan]

I was afraid of that.

I was thinking ther emight be a macro or some VB I could right to
open all linked files and close silently. Then in each file, docb,
docc, docd, I would have the same macro to open all linked files. So
when I open one file it auto opens all files and closes them without
the user seeing it.

Appreciate your help.

Hi Malic,

The thing is that even if you did that, and I think it is possible, it
is still likely to take quite a long time to open the files in
sequence, wait for them to update their links, and then close and save
them again (possibly without the save).

If you want to get help with coding for that though, I suggest you
start a new (specific subject) thread in the programming forum
(sorry - I'm not up to that level of VBA).

HTH,

Alan.

 

[Alan]

I was afraid of that.

I was thinking ther emight be a macro or some VB I could right to
open all linked files and close silently. Then in each file, docb,
docc, docd, I would have the same macro to open all linked files.
So when I open one file it auto opens all files and closes them
without the user seeing it.

Hi Malic,

The thing is that even if you did that, and I think it is possible, it
is still likely to take quite a long time to open the files in
sequence, wait for them to update their links, and then close and save
them again (possibly without the save).

That is likely to be a user issue in most situations - you would have
to survey your users I guess.

If you want to get help with coding for that though, I suggest you
start a new (specific subject) thread in the programming forum
(sorry - I'm not up to that level of VBA).

HTH,

Alan.

 

[jee22]

Have you considered creating a work book consisting of docs A to E.?

Joe