Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2014

    Unanswered: Excel: edit links on two places: Both filename and directory have new names. Macro??

    Hi all,

    Every month, I need to change 100+ links in Excel by using the edit link file. I cannot do this via Ctlr+H because both the directory and the filename have changed.
    Can someone please help me? Maybe write a macro for it or help me do it myself.

    FOr example in the file consolidation.xls I have 100+ links to other excel files that look like:
    C:\Users\Public\Documents\08.August\USA_ 0814.xls
    C:\Users\Public\Documents\08.August\Germany_ 0814.xls
    C:\Users\Public\Documents\08.August\France_ 0814.xls

    and I need to change it on 2 places, both the month in the directory and the filename itself, so it become:

    C:\Users\Public\Documents\09. September\USA_ 0914.xls
    C:\Users\Public\Documents\09. September\Germany_ 0914.xls
    C:\Users\Public\Documents\09. September\France_ 0914.xls

    I hope someone can help me. I am using Excel 2010.

    Many thanks,


  2. #2
    Join Date
    Jan 2002
    Bay Area
    Here is a macro I put together to create a list of Excel files based on the MMYY target found in the file name.
    The previous list is cleared out by the macro.
    Change it for your own file path and see if it works. My Excel version is 2003.
    Option Explicit
    Sub Update_Links()
    'prompts for month & year in YYMM format; search a specific folder for
    'Excel files with the target month & year in the file name.
    'add the list to Sheet1 column A
    'Note:  current contents in column A will cleared by this macro
    Dim docPath As String, month_year As String
    Dim fs As FileSearch
    Dim ws As Worksheet
    Dim i As Integer, j As Integer
    Set fs = Application.FileSearch
    Set ws = Sheets("Sheet1")
    docPath = "c:\Temp\Documents\"
    month_year = InputBox("Enter month & year in the format MMYY")
    If Len(month_year) = 0 Then Exit Sub
    If Not IsNumeric(month_year) Then
        MsgBox month_year & " is not in the form MMYY." & vbCrLf & "Exiting macro."
        Exit Sub
    End If
    With ws
        With fs
            .SearchSubFolders = False
            .FileType = msoFileTypeExcelWorkbooks
            .LookIn = docPath
            j = 0
            If .Execute > 0 Then
                For i = 1 To .FoundFiles.Count
                    If InStr(.FoundFiles(i), month_year) > 0 Then
                        j = j + 1
                        ws.Cells(j, 1).Value = .FoundFiles(i)
                    End If
            End If
        End With
    End With
    If j = 0 Then MsgBox "No files found in the folder " & vbCrLf & docPath _
        & vbCrLf & "that had " & month_year & " in the file name."
    End Sub
    Last edited by JerryDal; 11-21-14 at 15:34. Reason: remove unused declared variable

Tags for this Thread

Posting Permissions

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