Utility to repair Excel ‘broken links’ after file copy/rename?

[pdebee]

I am running Excel 2010 on Windows 7. I am quite familiar with Excel, but not in writing VBA macros. </SPAN>

General background to the query.</SPAN></SPAN>

I have seen many forum queries discussing the issue of the need to repair broken links in Excel spread sheets that were copied and/or renamed, although I have not seen any mentioning a workable solution. </SPAN></SPAN>

It seems to me that this issue would affect everyone who has file names embedded in the cells of Excel spread sheets and needs to copy/rename them for some reason, and that the problem becomes rapidly unmanageable for Excel users with large numbers of linked spread sheets. Are we really all supposed to re-invent the wheel by each writing our own bespoke utility? </SPAN></SPAN>

My specific case.</SPAN></SPAN>

In my current project, I am helping a friend who manufactures musical instruments by hand and who wants to run an experiment to track the time it takes him to execute the numerous (2,000+) micro-tasks involved in building a complete instrument from start to finish. So, I have designed a prototype for him, as a hierarchical suite of over 200+ linked</SPAN> Excel workbooks, each with a single worksheet. Each worksheet lists the sequence of micro-tasks required to build a specific part for an instrument, and the data collected by these worksheets is percolated upwards across a chain of other worksheets, with an end worksheet summarising the total time taken to manufacture the whole instrument. </SPAN>

For each different type of instrument, there is a generic folder for the whole suite of spread sheets. When an order is received, the generic folder and all its spread sheets get copied and renamed to reflect specific information (such as a unique order number and instrument name), based on a file naming convention.</SPAN>

But, of course, all the internal links between the 200+ spread sheets will be broken after all the files have been renamed. I know that it is possible to repair broken links by opening all the linked spread sheets and then use 'Edit links' and 'Change source' manually; however, we would have to repeat this laborious manual task n</SPAN> times (n</SPAN></SPAN> = number of linked cells in a whole folder, or >2,000 in my case) for each new instrument being ordered, and I need to find a way of automating this laborious process. </SPAN>

The ideal utility would receive two parameters: </SPAN>

1. the old file name (to locate in the copied spread sheet cells) of the 'generic' instrument folder, for example: </SPAN>

"C:\...\Documents\My Spreadsheets\Folder1\HG_XXX</SPAN>_INSTRUMENT</SPAN></SPAN>"</SPAN>

2. the new file name (to replace in-situ) in the resulting 'unique' instrument folder, for example:</SPAN>

"C:\...\Documents\My Spreadsheets\Folder1\HG_301</SPAN>_CALLOT</SPAN></SPAN>"</SPAN>

Of course, each file reference in a linked cell contains more than just the file name; also appended to the file name are: the workbook name, workbook sheet name and the cell details of the spread sheet containing the source data, for example:</SPAN>

"_C1P1_Blocks.xls]Sheet1'!$C$17"</SPAN>

but we would want the utility to ignore those details, because they would not need to be updated anyway; we would only want to update those elements of the links that represented the file names</SPAN>. </SPAN></SPAN>

In conclusion, it seems to me that my requirement is shared by any Excel user who has developed a large number of linked spread sheets that need to be replicated for some reason, or even simply moved from one system to another.</SPAN>

Any pointers to existing, workable solutions?

Thank you very much, in advance, for any pointer(s) to workable solution(s) that some of you may have implemented successfully, or existing products that some of you might have adopted in a home (single user) environment. </SPAN>

With kind regards; </SPAN>
Patrick.</SPAN>

 

[Jerry Sullivan]

Hi Patrick,

The sub: ConvertLinksToUNC in Post #10 of this thread is close to what you describe. It replaces the front end (Drive reference) of the link path and it could be modified into a function that replaces "oldFileName" with "newFileName"

http://www.mrexcel.com/forum/excel-...-file-paths-unc-not-mapped-drive-letters.html

A couple of notes regarding your OP...

1. The linked workbooks would not need to be open (it's a little better if they are not).

2. When you Edit a Link in the Edit Links dialog, it remaps all the formulas in the workbook using that link. So the number of cells in a workbook having external links doesn't create more effort to manually remap links- it's the number of unique external links in the workbook that drives that effort.

3. I'm sure you've already considered this and found a reason not to go that route, but in most applications it would be simpler to have all 200 worksheets in the same workbook instead of 200 separate workbooks with one sheet each.

 

[pdebee]

Hi Patrick,

The sub: ConvertLinksToUNC in Post #10 of this thread is close to what you describe. It replaces the front end (Drive reference) of the link path and it could be modified into a function that replaces "oldFileName" with "newFileName"

http://www.mrexcel.com/forum/excel-...-file-paths-unc-not-mapped-drive-letters.html

A couple of notes regarding your OP...

1. The linked workbooks would not need to be open (it's a little better if they are not).

2. When you Edit a Link in the Edit Links dialog, it remaps all the formulas in the workbook using that link. So the number of cells in a workbook having external links doesn't create more effort to manually remap links- it's the number of unique external links in the workbook that drives that effort.

3. I'm sure you've already considered this and found a reason not to go that route, but in most applications it would be simpler to have all 200 worksheets in the same workbook instead of 200 separate workbooks with one sheet each.

Hi Jerry,</SPAN></SPAN>

Very many thanks for your prompt and helpful reply; I really appreciate you taking the time.</SPAN></SPAN>

I now need to spend some time studying your sample code and use my reference books on VBA programming to learn exactly what the code is doing. Once I have understood this, I will need to learn and figure out how to extend your sample code into a watertight solution. </SPAN></SPAN>However, your sample has given me a very helpful start, for which I am grateful.</SPAN>

Thank you also for highlighting the benefit of grouping as many processes (worksheets) into a single workbook as possible. The reason for segregating the process of manufacturing each part into a separate worksheet was simply to make it easier for my friend to use the system:</SPAN></SPAN>

1. Walk to the laptop in the corner of his workshop</SPAN></SPAN>
2. Open the folder for that specific instrument</SPAN></SPAN>
3. Locate the file icon for the part being manufactured today (for example: ‘…_C1P1_Blocks’)</SPAN></SPAN>
4. Open the file & view the Excel worksheet</SPAN></SPAN>
5. Locate the appropriate task in the list of tasks (column ‘A’) required to build a block</SPAN></SPAN>
6. Position the cursor in column ‘B’ (‘Start Date & Time’) </SPAN></SPAN>
7. Press Ctrl+Shift+T to record the current date & time as a static value in the cell. </SPAN></SPAN></SPAN>
8. (Work on the task until completed…)</SPAN></SPAN>
9. Position the cursor in column ‘C’ (‘End Date & Time’) </SPAN></SPAN>
10. (Carry on with another task, or take a break)</SPAN></SPAN>

I wanted to shield him from running the risk of ‘getting lost in Excel’ while trying to locate the appropriate worksheet among the many that would have been populated within a massive workbook.</SPAN>

However, I will build a parallel prototype that works this way and learn from it, to validate the assumption. Thank you for the offering the hint, though.</SPAN>

With kind regards for now;</SPAN>
Patrick.</SPAN>

PS: In conclusion, it would appear that we are</SPAN> condemned, each of us individually, to re-invent the wheel! One would have thought that Microsoft would have recognised this issue and supplied such a utility, built as a function within Excel… </SPAN>

 

[pdebee]

Add code mentioned above by Jerry (for reference in future posts):

Sub ConvertLinksToUNC()
    Dim sUNC As String
    Dim vLinks As Variant
    Dim i As Long

    
    Const sDriveLetter As String = "G:"

    
    sUNC = GETNETWORKPATH(sDriveLetter)

    
    If sUNC = "" Then
        MsgBox "Drive letter " & sDriveLetter & " not mapped."
        Exit Sub
    End If

        
    With ActiveWorkbook
         vLinks = .LinkSources(xlExcelLinks)
         If Not IsEmpty(vLinks) Then
             For i = 1 To UBound(vLinks)
                 .ChangeLink Name:=vLinks(i), _
                    NewName:=Replace(vLinks(i), Find:=sDriveLetter & "\", _
                    Replace:=sUNC & "\", Compare:=vbTextCompare)
             Next i
         End If
    End With

    
End Sub