If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
word 2000 saving after updating links | John-G | General Discussions | 3 | February 17th, 2005 04:24 PM |
Updating Links | MDW | Worksheet Functions | 2 | August 31st, 2004 03:43 PM |
Links not updating automatically | Farzana Saleem | General Discussion | 3 | June 18th, 2004 01:34 PM |
Excel 2000 converts UNC links to relative - need to preserve UNC links! | Jeff Adams | Links and Linking | 3 | April 15th, 2004 05:46 PM |
Excel Links to files on network not updating | Bobbie Jo | Worksheet Functions | 0 | January 13th, 2004 09:02 PM |