automatically updating links (2003)

Topic

New Reply

Hello all,

We have a lot of Excel workbooks that contains links to other workbooks. But our IT department has the idea to change the global folderstructure of the networkdrive. This will mean that a workbook from the folder V:dep01 will move to the folder V:depsdep01.
When they start moving folders and files to other levels all the workbooks will lose the link between them.
Is there a way to automatically check every workbook in the folders and correct the links?

Tnaks in advance for your answers

Reply | Quote

Replies

As long as the relative location of the workbooks remains the same, Excel will gracefully resolve the links. For example, say you have a workbook V:dep01managementsummary.xls that refers to another workbook V:dep01surgerypatients.xls. After the change, you’ll have V:depsdep01managementsummary.xls and V:depsdep01surgerypatients.xls. Since the relative position of the workbooks has remained the same, the links should still work, without any work on your side.
If, however, the second workbook is moved to V:depsdep01surgerycardiacpatients.xls, the links would break because the relative position has changed by the introduction of an extra level for only one of the workbooks.

Reply | Quote

Thanks Hans,

Luckely 70% of the movements will be relative, but 30% will move to other locations.
Is it possible for those 30% to use a macro to correct the links?

Reply | Quote

No doubt it’s possible, but is there a fixed pattern to the changes, or will you have to specify the new path for individual files? It might help to prepare a worksheet that lists the changes.

Reply | Quote

Hans,

Yes, there is a pattern to te movement. It is possible to create a sheet with old en new locations just by foldernames.

Reply | Quote

Let’s say you create a sheet named List with the old path in column A and the corresponding new path in column B, starting in row 2.
Put the attached macro in a module in the workbook containing this sheet.
The macro will loop through all workbooks in the specified folder and its subfolders, and replace each of the specified strings.

*** WARNING: test the macro carefully on a test folder with copies of workbooks before running it on your real workbooks! ***

If the number of folders and workbooks to be processed is large, I’d recommend running the macro separately in different folders (by changing the line .LookIn = “V:depsdep01”) instead of trying to do it all at once from a “root” folder – VBA might well choke if you try that.

Reply | Quote

Cool Hans,

Thank you very much for your effort.
I will give it a test.

Reply | Quote

Hello Hans,

It seems to work correctly after I changed to following lines:

strOld = wshList.Range("A"  & r)
strNew = wshList.Range("B"  & r)

You used the brackets in your code, but then you are replacing the file name and not the foldername
Thanks again for your help

Reply | Quote

Sorry about that, it was just air code. Glad you were able to correct it yourself!

Reply | Quote

HAns,

Just another question about this.
Is it possible when opening workbooks with a for/next loop to check when opening a workbook if that workbook has external links at al?
If you look at the Excel menu ,edit, links (I’m not sure about the correct english menu names) you see an overview of the links used by that workbook.
Is it possible to read those links in a variable?

Reply | Quote

The LinkSources property of a workbook is either empty (if there are no links), or an array of strings describing the links. You can use code like this:

Dim arrLinks
Dim i As Integer
arrLinks = ActiveWorkbook.LinkSources
If IsEmpty(arrLinks) Then
Debug.Print “No links!”
Else
For i = LBound(arrLinks) To UBound(arrLinks)
‘ Do something with the link string
Debug.Print arrLinks(i)
Next i
Erase arrLinks
End If

Reply | Quote

Again Hans,
Thanks for sharing you knowledge

Reply | Quote