PDA

View Full Version : Solved: macro to edit links



agnesz
08-16-2007, 01:38 PM
I have an add-in which cumulates all sheets in the currently open workbook named LocSum (which is saved to my desktop). I'd like to be able to share it with others, but their file name and its location may be different. What code can I add to the beginning of my macro which opens the edit links dialogue box and prompts user to change source and then updates the sheet?
Any thoughts?
Thank you!
az

geekgirlau
08-20-2007, 09:17 PM
Sub RefreshLink()
Dim varNewLink As Variant
Dim lnk As Variant
Dim i As Integer

' get all links
lnk = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(lnk) Then
' prompt for the new file for the link
varNewLink = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
' if user didn't cancel, refresh the link
If varNewLink <> False Then
For i = 1 To UBound(lnk)
ActiveWorkbook.ChangeLink Name:=lnk(i), NewName:=varNewLink, _
Type:=xlExcelLinks
Next i
End If
End If
End Sub

agnesz
08-21-2007, 11:56 AM
This is awesome! thank you so much
:friends: