Question about link
 

I have one master workbook and several sub-workbooks. All the sub-workbooks
get assumptions from the master workbook and return the result to master
workbook. Is there a way to automatically update all the sub-workbooks after
I change some assumptions in the master workbook and return the new result to
master workbook?

I've tried Edit/Link/Update now in the master workbook after I changed the
assumptions, it doesn't work.

Any help will be greatly appreciated.

Jason

When you open those subworkbooks, you'll be prompted (if you use that setting)
to see if you want to update the links.

So you could open, update the links and save/close.

(Or just wait until you really want to use that subworkbook.)

Jason wrote:

I have one master workbook and several sub-workbooks. All the sub-workbooks
get assumptions from the master workbook and return the result to master
workbook. Is there a way to automatically update all the sub-workbooks after
I change some assumptions in the master workbook and return the new result to
master workbook?

I've tried Edit/Link/Update now in the master workbook after I changed the
assumptions, it doesn't work.

Any help will be greatly appreciated.

Jason

--

Dave Peterson

Links are not bi-directional. You would need to create a link from Wbk A to B
and a separate link from B to A. (It sounds like that is already the case,
just needs to be expanded on.) Not sure, but it is possible you may wind up
with circular references if you overdo it. |:)

"Jason" wrote:

I have one master workbook and several sub-workbooks. All the sub-workbooks
get assumptions from the master workbook and return the result to master
workbook. Is there a way to automatically update all the sub-workbooks after
I change some assumptions in the master workbook and return the new result to
master workbook?

I've tried Edit/Link/Update now in the master workbook after I changed the
assumptions, it doesn't work.

Any help will be greatly appreciated.

Jason

Dave,

Is there a way to update the link without opening the subworkbooks. Not mean
to be lazy, about 30 subworkbooks supporting the master workbook, and am
trying to do some scenario analysis.

Thanks a lot.

Jason

"Dave Peterson" wrote:

When you open those subworkbooks, you'll be prompted (if you use that setting)
to see if you want to update the links.

So you could open, update the links and save/close.

(Or just wait until you really want to use that subworkbook.)

Jason wrote:

I have one master workbook and several sub-workbooks. All the sub-workbooks
get assumptions from the master workbook and return the result to master
workbook. Is there a way to automatically update all the sub-workbooks after
I change some assumptions in the master workbook and return the new result to
master workbook?

I've tried Edit/Link/Update now in the master workbook after I changed the
assumptions, it doesn't work.

Any help will be greatly appreciated.

Jason

--

Dave Peterson

The subworkbooks get that information from the parent workbook?

And you want to retrieve that same information from the subworkbooks????

I think that the workbooks have to be opened. But you could open them with a
macro and then save/close them.

I built a list of the workbook names in A2:A31 (about) and ran this macro:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim TempWkbk As Workbook
Dim testStr As String

With Worksheets("sheet1")
'headers in row 1
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
testStr = ""
On Error Resume Next
testStr = Dir(myCell.Value)
On Error GoTo 0
If testStr = "" Then
MsgBox "typo in: " & myCell.Address(0, 0)
Else
Set TempWkbk = Workbooks.Open(Filename:=myCell.Value,
UpdateLinks:=1)
TempWkbk.Close savechanges:=True
End If
Next myCell

End Sub





Ah....Maybe the subworkbooks do a lot of calculation with that information
that's pushed to them via the links and you're using the results of that
calculation????

Jason wrote:

Dave,

Is there a way to update the link without opening the subworkbooks. Not mean
to be lazy, about 30 subworkbooks supporting the master workbook, and am
trying to do some scenario analysis.

Thanks a lot.

Jason

"Dave Peterson" wrote:

When you open those subworkbooks, you'll be prompted (if you use that setting)
to see if you want to update the links.

So you could open, update the links and save/close.

(Or just wait until you really want to use that subworkbook.)

Jason wrote:

I have one master workbook and several sub-workbooks. All the sub-workbooks
get assumptions from the master workbook and return the result to master
workbook. Is there a way to automatically update all the sub-workbooks after
I change some assumptions in the master workbook and return the new result to
master workbook?

I've tried Edit/Link/Update now in the master workbook after I changed the
assumptions, it doesn't work.

Any help will be greatly appreciated.

Jason

--

Dave Peterson


--

Dave Peterson