Find links to other worksheets using VBScript?

[The AD Designer]

Hello,

I have a quandry, I am moving excel files between servers and would like to
impact assess the links in excel spreadsheet to other workbooks. The links
have for instance <drive letter>:\folder name\[test.xls]Sheet1'!$A$1, if the
drive letter changed i would like to have some code which i can run against
the file which will update the links to point to the correct location. But i
also need some script which will display the link and whats it's point to.

anyone done this before? Thanks

 

[arjen van...]

The way I've done it is by creating an array of links and using Debug.Print
to display the links in the Immediate window of the VBE (Ctrl+G).

Sub AllLinks()

'create and define an array of the links in the spreadsheet
Dim AllLinks As Variant

AllLinks = ThisWorkbook.LinkSources(xlExcelLinks)

'check if there are any links
If Not IsEmpty(AllLinks) Then

'if there are links define an integer to count the number of links
'from the lower to the upper bound of the array
Dim i As Integer

For i = LBound(AllLinks) To UBound(AllLinks)

'print the links to the immediate window (ctrl + g)
Debug.Print AllLinks(i)

'go to the next link
Next i

'if there are no links in the workbook, the message box will inform the
user
Else
MsgBox Prompt:="There are no links in this workbook", Title:="NO
LINKS"

End If

End Sub

One thing to note, you can get around the problem of changing drive letters
by using the server name in the links rather than the drive letters
(\\servername\folder\etc...)