Loading
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
    Posts
    1
    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

    Excel Video Tutorials / Excel Dashboards Reports


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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,787
    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

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, 18:01
  2. Link Cells In Multiple Worksheets?
    By kemrich in forum EXCEL HELP
    Replies: 1
    Last Post: May 22nd, 2007, 06:25
  3. Test Auto Link
    By Dave Hawley in forum Test Area
    Replies: 1
    Last Post: May 19th, 2007, 20:38
  4. Processing Data In Multiple Folders
    By AFIQ in forum EXCEL HELP
    Replies: 10
    Last Post: March 14th, 2007, 06:07
  5. Replies: 4
    Last Post: January 31st, 2007, 11:41

Bookmarks

Posting Permissions

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