Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: Mass changing Excel links??

    One of our accounting folks would like to change the name of a directory, which contains MANY MANY excel spreadsheets in various subdirectories. Most of these spreadsheets contain links to another spreadsheet/s which contains the name of the parent directory (the name she wants to change) in the actual link: i.e the link is \\namethatisbeingchanged\spreadsheet.xls

    Is there a way to change the links in all the spreadsheets to reflect the new name of the parent directory without having to manually change the link in each spreadsheet? Some kind of script or something? Or is this just not possible?
    Last edited by xpac; 12-10-04 at 12:26.

  2. #2
    Join Date
    Dec 2004
    Posts
    3
    BUMP...Anyone???

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    If the links are in formulas you can use code like this to run on each workbook. The simplest thing would be to put this in a workbook and attach a custom toolbar and button to activate the Sub Proc 'UpdateFormulas()' then Hide and Save the workbook so when you open it, it will not be visible. Then when you open a book needing path updates you can click the button, to update the formulas on the current active book. This is a simple example you can elaborate to loop through worksheets or get the files in the directory and open each file, update links for wholesale changes.

    Code:
    Const OldPath = "C:\NewPath\"
    Const NewPath = "C:\Old Path\"
    
    Sub UpdateFormulas()
        Dim FormulaCells As Range, Cell As Range
        Dim strFormula As String
        Dim curFormula As String
        
    '   Create a Range object for all formula cells
        On Error Resume Next
        Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
        
    '   Exit if no formulas are found
        If FormulaCells Is Nothing Then
            MsgBox "No Formulas."
            Exit Sub
        End If
        
        
    '   Process each formula
        For Each Cell In FormulaCells
                curFormula = Cell.Formula
                If InStr(1, curFormula, OldPath) > 0 Then
                   strFormula = Replace(curFormula, OldPath, NewPath)
                   Cell.Formula = strFormula
                End If
        Next Cell
       
    End Sub
    The above example was based on the code from
    http://j-walk.com/ss/excel/tips/tip37.htm

    /
    Last edited by savbill; 12-14-04 at 18:21.
    ~

    Bill

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    In addition to Bills post

    Here is a way to run through each workbook in turn
    Code:
    Sub GetallWorkbooks()
    Dim wkbFound As Workbook
    Dim wksFound As Worksheet
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        'find all files within the new path
        With Application.FileSearch
            .NewSearch
            .LookIn = NewPath
            .SearchSubFolders = True
            .FileType = msoFileTypeExcelWorkbooks
            If .Execute > 0 Then
                For i = 1 To .FoundFiles.Count
                    Set wkbFound = Workbooks.Open(.FoundFiles(i))
                    'update all formulas in each worksheet of the found workbook
                    For Each wksFound In wkbFound.Worksheets
                        wksFound.Activate
                        Call UpdateFormulas
                    Next wksFound
                    wkbFound.Save
                    wkbFound.Close
                Next i
            End If
        End With
                    
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub
    Dave

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Can't you use the 'Change Links' option from the Edit->Links.. toolbar dialogue box, after the new directiory stucture has been estabished ?

    MTB

  6. #6
    Join Date
    Dec 2004
    Posts
    3
    Quote Originally Posted by MikeTheBike
    Hi

    Can't you use the 'Change Links' option from the Edit->Links.. toolbar dialogue box, after the new directiory stucture has been estabished ?

    MTB
    Mike, yes that would work but I'm talking about over a hundred spreadsheets so doing it manually would take quite a bit of time.

    Thanks for the replies everyone, I'll give your suggestions a try.

  7. #7
    Join Date
    Nov 2009
    Posts
    4
    Hello,

    To resurrect this old thread, it is proving very useful.. but is there a way to override excel asking you if you want to update all links in the workbook when it opens? I would like to say Don't Update.. but no idea how to implement.

    http://img526.imageshack.us/img526/976/dontupdate.jpg


    Thanks

    EMily

  8. #8
    Join Date
    Nov 2009
    Posts
    4
    Ok, just sorted it.

    Many thanks to SavBill and DavidCoutts again... code saves us a lot of time!

    Sub GetallWorkbooks()
    Dim wkbFound As Workbook
    Dim wksFound As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'find all files within the new path
    With Application.FileSearch
    .NewSearch
    .LookIn = NewPath
    .SearchSubFolders = True
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
    For i = 1 To .FoundFiles.Count
    Set wkbFound = Workbooks.Open(.FoundFiles(i), False)
    'update all formulas in each worksheet of the found workbook
    For Each wksFound In wkbFound.Worksheets
    wksFound.Activate
    Call UpdateFormulas
    Next wksFound
    wkbFound.Save
    wkbFound.Close
    Next i
    End If
    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub

  9. #9
    Join Date
    Nov 2009
    Posts
    4
    Hi,

    Okay sorry to be bringing this up again... but turns out there is quite a few workbooks throwing up Circular Reference errors upon opening.. is there a way to code in an auto 'Cancel' for the error alert?

    Thanks




    Quote Originally Posted by smemflynn View Post
    Ok, just sorted it.

    Many thanks to SavBill and DavidCoutts again... code saves us a lot of time!

    Sub GetallWorkbooks()
    Dim wkbFound As Workbook
    Dim wksFound As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'find all files within the new path
    With Application.FileSearch
    .NewSearch
    .LookIn = NewPath
    .SearchSubFolders = True
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
    For i = 1 To .FoundFiles.Count
    Set wkbFound = Workbooks.Open(.FoundFiles(i), False)
    'update all formulas in each worksheet of the found workbook
    For Each wksFound In wkbFound.Worksheets
    wksFound.Activate
    Call UpdateFormulas
    Next wksFound
    wkbFound.Save
    wkbFound.Close
    Next i
    End If
    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub

  10. #10
    Join Date
    Nov 2009
    Posts
    4
    Went to another forum as was in a mega rush:

    Code:
    Good afternoon smemflynn
    
    ...and welcome to the forum!!
    Quote:
    Originally Posted by smemflynn View Post
    is there a way to code in to auto-cancel the circular references warning when they occur and so edit the paths in the offending workbooks?
    Untested, but ...
    
    There isn't an instruction or setting per se that will do what you require, but you could try altering the calculation settings. You could use some code such as that below before the workbook is opened that will effectively instruct Excel will ignore the circular reference and calculate the formula 10 times, after which it will place the result in a cell.
    
    Code:
    
    
    Code:
    With Application
        .Iteration = True
        .MaxIterations = 10
        .MaxChange = 0.001
    End With
    HTH DominicB
    Works a treat!

Posting Permissions

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