Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find links to other worksheets using VBScript?
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 -- The AD Designer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find links to other worksheets using VBScript?
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...) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links between worksheets and more... | Excel Worksheet Functions | |||
Worksheets.add in VBScript overwrites existing worksheet | Excel Worksheet Functions | |||
How do I Find links in worksheets - not in any formula | Excel Worksheet Functions | |||
Links to other worksheets | Excel Worksheet Functions | |||
can't update links...can't find links | Excel Discussion (Misc queries) |