inspect multiple excel files for linked referances

[nassety]

Hi

I have many many excel files , all of which have linked data. is there a simple vb script that will in spect an excel file with the linked information starting with "a known path" and replace the front part of the path with a simple change? basically there file structure has changed but its almost the same except for the very start of the path. all i need to do is replace the front of the path. it seems to me all i need to find is "\\path of old structure\" and ammend the front with "\\new path\"
thanks in advance any help anyone can give me.

 

[Gavona]

Welcome to Tek-Tips.
Have a look at:
LinkSources Method
ChangeLink Method
Replace function
Here is some code I have to open all linked workbooks (might help get you started):

Sub OpenAllLinks()
    Dim arLinks As Variant
    Dim intIndex As Integer
    Dim MyWbk As Workbook
    Set MyWbk = ActiveWorkbook
    arLinks = MyWbk.LinkSources(xlExcelLinks)
   
    If Not IsEmpty(arLinks) Then
        For intIndex = LBound(arLinks) To UBound(arLinks)
            MyWbk.OpenLinks arLinks(intIndex)
        Next intIndex
    Else
        MsgBox "The active workbook contains no external links."
    End If
    MyWbk.Activate
End Sub

Please post your code once you have it working - or post back for more help, including the code you have tried.

Gavin

 

[eVAPor8]

May I suggest a possible "simple" solution?

It sounds like you have a load of Linked formulae along the lines of..

='\\oldServer\oldShare\some\path\to\workbook\book1.xls]Sheet1'!A1

branching out to a load of sheets a new Network Share...

...So can't you just Find&Replace on Formulas and replace \\oldServer\OldShare with \\NewServer\NewShare\ or am I being too obvious?


I used to be the Comaboy on Tek-Tips.

...And the despicable Jeremy Vyle elsewhere.

 

[Gavona]

Btw neither solution will re-direct named ranges where the definition of the range refers to another book.

If you have many links then it would be sensible to open the linked workbook first. This code does all that.

Sub Changelink()
    Dim arLinks As Variant
    Dim intIndex As Integer
    Dim MyWbk As Workbook
    Dim LinkedWbk As Workbook
    Dim strFind As String, strReplace As String, strNew As String
    
    strFind = "C:\Fin....."
    strReplace = "C:\users\........."
    
    Set MyWbk = ActiveWorkbook
    arLinks = MyWbk.LinkSources(xlExcelLinks)
   
    If Not IsEmpty(arLinks) Then
        For intIndex = LBound(arLinks) To UBound(arLinks)
        
        strNew = Replace(arLinks(intIndex), strFind, strReplace)
        strNew = InputBox(arLinks(intIndex) & " will be replaced with:", "Change Link", strNew)

'if file does not exist then skip this link
            If Not FileExists(strNew) Then
                    MsgBox strNew & " was not located.  Link will not be updated", vbInformation, MyWbk.Name
                    GoTo mynext
            Else
                Workbooks.Open Filename:=strNew, UpdateLinks:=False, ReadOnly:=True
                Set LinkedWbk = ActiveWorkbook
                MyWbk.Activate
                MyWbk.Changelink arLinks(intIndex), strNew
                LinkedWbk.Close savechanges:=False
            End If
mynext:
        Next intIndex
    Else
        MsgBox "The active workbook contains no external links."
    End If
    MyWbk.Activate

End Sub

Function FileExists(strLongFilename As String)
Dim fso
Dim file As String
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(strLongFilename) Then
    'MsgBox file & strLongFilename & " linked file was not located.", vbInformation, "File Not Found"
    FileExists = False
Else
    'MsgBox file & strLongFilename & " linked file has been located.", vbInformation, "File Found"
    FileExists = True
End If
End Function

I have not the experience to advise you how to step through all files and subfolders in a folder but your original post suggests that might be desirable. If you need that then ask and someone will no doubt help.



Gavin