Unanswered: Excel: edit links on two places: Both filename and directory have new names. Macro??
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:
and I need to change it on 2 places, both the month in the directory and the filename itself, so it become:
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.
'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."
.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)
If j = 0 Then MsgBox "No files found in the folder " & vbCrLf & docPath _
& vbCrLf & "that had " & month_year & " in the file name."
Last edited by JerryDal; 11-21-14 at 15:34.
Reason: remove unused declared variable