Changing the path to linked excel worksheets programatically

I have a requirement to change the path (name) to a number of linked worksheets by code.
I think it is something to do with the workbook.LinkSources but I am having some problems.

Am I looking at the wrong thing?  What is the property I should be loooking at?  Best of all - does someone have a piece of code to do it and is willing to share it?
LVL 45
AndyAinscowFreelance programmer / ConsultantAsked:
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
For example:
Sub ChangeLinkPaths(strOldPath As String, strNewPath As String)
   Dim arrLinks, I As Long
   arrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
   If Not IsEmpty(arrLinks) Then
      On Error Resume Next
      Application.DisplayAlerts = False
      For I = LBound(arrLinks) To UBound(arrLinks)
         If InStr(1, arrLinks(I), strOldPath, vbTextCompare) > 0 Then
            ActiveWorkbook.ChangeLink arrLinks(I), Replace$(arrLinks(I), strOldPath, strNewPath), xlLinkTypeExcelLinks
         End If
      Next I
      Application.DisplayAlerts = True
   End If
End Sub

Open in new window

assuming I understood your question correctly.
AndyAinscowFreelance programmer / ConsultantAuthor Commented:
Excellent, works like a dream.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.