Auto re-link worksheets in multiple folders

[Mahen331]

hi,

I need help sevearlly on how to automatically re - link my existing excel files which are in multiple folders, and recently moved to a new server (which has a completly new parth names/Server names.). The Files contain no of linkage to other worksheets in different folders.


Is there a faster way of re mapping of these broken links..?, Please help.I simply do not want to open the files one by one and do it mannually

[Neale]

Couple of ways

When you open the sheets excel will ask to re-establish the links just tell it where the files are. Just ioopen each file - you don't need to relink every cell.

or

use find/replace to change your link references

I don't think server names make a difference as excel just uses the drive letters

[Dave Hawley]

Hi Mahen331

Are you aware of the Edit>links>Change source option?

The code below will work, just tweek to suit

Sub RunCodeOnAllXLSFiles() 
    Dim i As Integer 
    Dim wbResults As Workbook 
    Dim wbCodeBook As Workbook 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.EnableEvents = False 
     
    On Error Resume Next 
     
    Set wbCodeBook = ThisWorkbook 
     
    With Application.FileSearch 
        .NewSearch 
        'Change path To suit 
        .LookIn = "C:\MyDocuments\TestResults" 
        .FileType = msoFileTypeExcelWorkbooks 
         
        If .Execute > 0 Then 'Workbooks In folder 
        For i = 1 To .FoundFiles.Count 'Loop through all 
            'Open Workbook x And Set a Workbook variable To it 
            Set wbResults = Workbooks.Open(.FoundFiles(i)) 
             
            'YOU EDIT LINKS CODE. Change To suit 
            ChDir "C:\Ozgrid Likom\ExcelStuff" 
            wbResults.ChangeLink Name:="C:\Ozgrid Likom\Testings\Book2.xls", _ 
            NewName:="C:\Ozgrid Likom\ExcelStuff\DataValidationSamples.xls", _ 
            Type:=xlExcelLinks 
             
            wbResults.Close SaveChanges:=True 
             
        Next i 
    End If 
End With 
On Error Goto 0 
Application.ScreenUpdating = True 
Application.DisplayAlerts = True 
Application.EnableEvents = True 
End Sub