FIX BROKEN LINKS EXCEL HOW TO
Once I have understood this, I will need to learn and figure out how to extend your sample code into a watertight solution. 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. Very many thanks for your prompt and helpful reply I really appreciate you taking the time. 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. 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.Īny pointers to existing, workable solutions?
FIX BROKEN LINKS EXCEL UPDATE
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:īut 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. "C:\.\Documents\My Spreadsheets\Folder1\HG_ 301_ CALLOT" the new file name (to replace in-situ) in the resulting 'unique' instrument folder, for example: "C:\.\Documents\My Spreadsheets\Folder1\HG_ XXX_ INSTRUMENT"Ģ.
the old file name (to locate in the copied spread sheet cells) of the 'generic' instrument folder, for example: The ideal utility would receive two parameters: ġ.
FIX BROKEN LINKS EXCEL MANUAL
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 times ( n = 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. 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.īut, of course, all the internal links between the 200+ spread sheets will be broken after all the files have been renamed.
įor each different type of instrument, there is a generic folder for the whole suite of spread sheets. 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. So, I have designed a prototype for him, as a hierarchical suite of over 200+ linked Excel workbooks, each with a single worksheet. 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. Are we really all supposed to re-invent the wheel by each writing our own bespoke utility? 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. 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. I am quite familiar with Excel, but not in writing VBA macros.