non updating links

Hi
I have a master workbook and a number of data entry workbooks contained in a
network folder.

The master workbook links to data on the data entry workbooks and the data
entry workbooks contain charts which are linked back to the master workbook
and calculated data based on the original data entry workbook data. I bit
circular, I know.

The intention is data is entered on the data entry workbook, a macro is run
and the charts are updated. The macro opens, then closes the master sheet.
However, the update only works intermittently; yesterday it only worked on my
PC and not on others (same version: Excel 2000), today it doesn't work at
all. It appears to stall at the update of the master from the data entry
sheet as when I open this file it still has the old values.

The only way I can reliably update the chart is to manually open the master
workbook at the same time as the data entry workbook, or open each workbook
individually (master first), updating links on each.


The macro code I am currently using is:
Sub Update_Graphs()
Workbooks.Open Filename:= _
"G:\Performance Measures\Master.xls", UpdateLinks:=3
Workbooks("Master.xls").Activate
Workbooks("Data Entry 1.xls").Activate
Workbooks("Master.xls").Activate
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

I have tried various sequences of the Activate function and the UpdateLinks
parameter. Changing from a UNC file address to G:\ seemed to fix yesterday's
problem.

Any clues ?

Thanks in advance.

Andrew S wrote:
I have tried various sequences of the Activate function and the UpdateLinks
parameter. Changing from a UNC file address to G:\ seemed to fix yesterday's
problem.


I don't think Activate will do anything for you.

If calculation mode is automatic then, if the links are correctly formed, the
updates should all occur on opening the master workbook.

Might be worth including
Application.Calculate
in the procedure just to be sure of the first.

So we are left with the links being badly formed, in the sense that Excel does
not realise that they are addressing the other workbook.

You are probably on the right track with UNC vs drivename filenames.
Links to files in the same directory are effectively held as relative links (ie
they should work regardless of the path used to access that directory).
However, if one workbook thinks it came from \\Server\dir and the other thinks
it came from G:\dir they may not be able to tell that the directories are the
same and thereby make the links between the two open workbooks.

I would suggest changing the Open line to
Workbooks.Open Filename:= _
ThisWorkbook.Path & "\Master.xls", UpdateLinks:=3
which will ensure that both workbooks think they were loaded from the same
directory.

If the links in one or other file are to the wrong form of directory they may
need to be updated. For example, in the data entry workbook (which only has one
link source?).. something like this (untested):
Dim V
V = ThisWorkbook.LinkSources(xlExcelLinks)
If LCase(V(LBound(V)))LCase(ThisWorkbook.Path & "\Master.xls") Then
ThisWorkbook.ChangeLink V(LBound(V)), ThisWorkbook.Path & "\Master.xls"
End If

For the links in the master it is a bit more complicated because there are
multiple sources.
Dim I As Integer
V = Workbooks("Master.xls").LinkSources(xlExcelLinks)
For I=LBound(V) To UBound(V)
If LCase(Right(V(I),Len(ThisWorkbook.Name)+1))="\" &
LCase(ThisWorkbook.Name) Then
Workbooks("Master.xls").ChangeLink V(I), ThisWorkbook.FullName
End If
Next

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks Bill,

the "ThisWorkbook.Path & "\Master.xls", " did the trick.

Andrew


"Bill Manville" wrote:

Andrew S wrote:
I have tried various sequences of the Activate function and the UpdateLinks
parameter. Changing from a UNC file address to G:\ seemed to fix yesterday's
problem.


I don't think Activate will do anything for you.

If calculation mode is automatic then, if the links are correctly formed, the
updates should all occur on opening the master workbook.

Might be worth including
Application.Calculate
in the procedure just to be sure of the first.

So we are left with the links being badly formed, in the sense that Excel does
not realise that they are addressing the other workbook.

You are probably on the right track with UNC vs drivename filenames.
Links to files in the same directory are effectively held as relative links (ie
they should work regardless of the path used to access that directory).
However, if one workbook thinks it came from \\Server\dir and the other thinks
it came from G:\dir they may not be able to tell that the directories are the
same and thereby make the links between the two open workbooks.

I would suggest changing the Open line to
Workbooks.Open Filename:= _
ThisWorkbook.Path & "\Master.xls", UpdateLinks:=3
which will ensure that both workbooks think they were loaded from the same
directory.

If the links in one or other file are to the wrong form of directory they may
need to be updated. For example, in the data entry workbook (which only has one
link source?).. something like this (untested):
Dim V
V = ThisWorkbook.LinkSources(xlExcelLinks)
If LCase(V(LBound(V)))LCase(ThisWorkbook.Path & "\Master.xls") Then
ThisWorkbook.ChangeLink V(LBound(V)), ThisWorkbook.Path & "\Master.xls"
End If

For the links in the master it is a bit more complicated because there are
multiple sources.
Dim I As Integer
V = Workbooks("Master.xls").LinkSources(xlExcelLinks)
For I=LBound(V) To UBound(V)
If LCase(Right(V(I),Len(ThisWorkbook.Name)+1))="\" &
LCase(ThisWorkbook.Name) Then
Workbooks("Master.xls").ChangeLink V(I), ThisWorkbook.FullName
End If
Next

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup