Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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,709
    Hi Mahen331

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

    The code below will work, just tweek to suit


    VB:
    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, 17:01
  2. Link Cells In Multiple Worksheets?
    By kemrich in forum EXCEL HELP
    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 HELP
    Replies: 10
    Last Post: March 14th, 2007, 05:07
  5. Replies: 4
    Last Post: January 31st, 2007, 10: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