Announcement

Collapse
No announcement yet.

Auto re-link worksheets in multiple folders

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Auto re-link worksheets in multiple folders



    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

  • #2
    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
    There are three types of people in this world.
    Those who can count and those who can't.

    Comment


    • #3


      Hi Mahen331

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

      The code below will work, just tweek to suit


      Code:
      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

      Comment

      Working...
      X