-
Solved: macro to edit links
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
-
[VBA]
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
[/VBA]
-
This is awesome! thank you so much
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules