Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: Auto re-link worksheets in multiple folders

  1. #1
    Join Date
    26th February 2003

    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

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    24th January 2003
    Perth, Australia
    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.


    use find/replace to change your link references

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

    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    24th January 2003
    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 
            '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", _ 
                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 

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. With Auto Link Conflict
    By Dave Hawley in forum Test Area
    Replies: 0
    Last Post: July 25th, 2007, 17:01
  2. Link Cells In Multiple Worksheets?
    By kemrich in forum Excel General
    Replies: 1
    Last Post: May 22nd, 2007, 05:25
  3. Test Auto Link
    By Dave Hawley in forum Test Area
    Replies: 1
    Last Post: May 19th, 2007, 19:38
  4. Processing Data In Multiple Folders
    By AFIQ in forum Excel General
    Replies: 10
    Last Post: March 14th, 2007, 05:07
  5. Replies: 4
    Last Post: January 31st, 2007, 11:41


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts