absolute links to a source file
Posted by dm on August 06, 2001 4:16 PM
I have several worksheets which pull data from other sheets. I need these to be constantly live with up-to-date data. The problem is, every week I save copies of the source files for archive purposes, and the links go with the saved version, rather than staying with the master version. For example, I go to look at my sales summary at the end of July and I find that the numbers are not being pulled from "Sales worksheet.xls" but from "Sales Worksheet Snapshot June 5.xls". I wonder if I can put anything in the link to to make it look for the data in the same place, even if I "save as" the source file. Help please.
Posted by Steve Martindale on August 07, 2001 3:52 AM
I had the same problem using files across a network. The files are kept on my computer and when my colleague accessed them, Excel insisted on changing all the formulae to include the full network path to the link files, which meant that my machine couldn't find them.
Microsoft, bless 'em(or something!) couldn't help, so I eventually came up with the following, called by a Private sub workbook_open
Sub CorrectLinks()
Dim Sh As Variant
Dim index As Integer
Dim Links As Variant
Links = ActiveWorkbook.LinkSources
If Not IsEmpty(Links) Then
Application.Calculation = xlManual
For index = LBound(Links) To UBound(Links)
If Right(Links(index), 13) _ = "Personnel.xls" And Len(Links(index)) > 13 Then
ActiveWorkbook.ChangeLink Links(index), Workbooks("Personnel.xls").Path & "\" & "Personnel.xls" '
End If
Next index
Application.Calculation = xlAutomatic
End If
End Sub
This removes the path, only leaving the file name. I'm sure you could adapt it. email me if you need a hand
