Excel 2010 - modify file link paths in VBA

I want to create a button that will run code to fix a file path for a certain file link.
Can anyone provide some VBA code assistance to get to the path and fix it? (I'm assuming then update the info)

Per the attached image: I get to the Edit Links box via the File Tab then the Edit Links to Files selection in the bottom right column.  I want to change this path with VBA and a button.

THANKS!!!
excel-links2.jpg
gpchicago08Asked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Corected code

Sub Link_Update()
 NewPath = "d:\MyPath"
 alink = ThisWorkbook.LinkSources
 If IsEmpty(alink) Then
 MsgBox "No links found"
 Else
    For Idx = 1 To UBound(alink)
        If alink(Idx) Like "*GP-ProjectList.xlsm" Then
            ChDir NewPath
            ActiveWorkbook.ChangeLink alink(Idx), _
                NewPath & Mid(alink(Idx), InStrRev(alink(Idx), "\")), _
                xlExcelLinks
        End If
    Next
 End If
 End Sub

Open in new window

0
 
ProfessorJimJamCommented:
Can you try this one

Sub UpdateLinks()
     Dim myLinks As Variant, i As Integer
     myLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
     If Not IsEmpty(myLinks) Then
         On Error Resume Next
         For i = LBound(myLinks) To UBound(myLinks)
             Workbooks.Open myLinks(i)
             If Err <> 0 Then
                 Err.Clear
             Else
                 ActiveWorkbook.Close False
             End If
             'ActiveWorkbook.UpdateLink Name:=myLinks(i), Type:=xlExcelLinks
         Next i
     End If
End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try

Sub Link_Update()
 NewPath = "d:\MyPath"
 alink = ThisWorkbook.LinkSources
 If IsEmpty(alink) Then
 MsgBox "No links found"
 Else
    For Idx = 1 To UBound(alink)
        If alink(Idx) Like "*GP-ProjectList.xlsm" Then
            ChDir NewPath
            ActiveWorkbook.ChangeLink alink(1), _
                NewPath & Mid(alink(Idx), InStrRev(alink(Idx), "\")), _
                xlExcelLinks
        End If
    Next
 End If
 End Sub

Open in new window

Regards
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gpchicago08Author Commented:
OH SO CLOSE!

For some reason this is replacing both links with the same file.  I have two links in this file.
GP-HolidayDates.xlsx
GP-ProjectList.xlsm

When I run this routine, it is replacing both the project list and the holiday dates with only the project list.

Can it be modified to replace each file link's path with the same path?

Here's what I included in the button_click event:
Sub FixLinksButton_Click()

 NewPath = "X:\INTRANET.FILES\01_Human Resources"
 alink = ThisWorkbook.LinkSources
 If IsEmpty(alink) Then
 MsgBox "No links found"
 Else
    For Idx = 1 To UBound(alink)
        If alink(Idx) Like "*GP-ProjectList.xlsm" Then
            ChDir NewPath
            ActiveWorkbook.ChangeLink alink(1), NewPath & Mid(alink(Idx), InStrRev(alink(Idx), "\")), xlExcelLinks
        End If
    Next
 End If

End Sub

Open in new window

0
 
gpchicago08Author Commented:
Sorry Rgonzo - still happening.  I wish I knew more VBA to help.  But it is still changing both file link paths
0
 
gpchicago08Author Commented:
Wait... I think it does work now.
0
 
gpchicago08Author Commented:
Well - The only thing that really worked was un-protecting the sheet with the file links.  Once I unproteced the sheet, the problem vanishes.  So...I've hidden the sheet.  That's really all that can work.  Even VBA code will not work on protected sheets (AFAIK).

Thanks M$.
0
 
gpchicago08Author Commented:
Thanks for the code assistance!  It worked.... just not on a protected worksheet.  Booooo.
0
 
Rgonzo1971Commented:
Hi,

you can unprotect throught VBA

Sheets("Sheet1").Unprotect "myPW"   ' "myPW" if necessary
' your code
Sheets("Sheet1").Protect "myPW"
0
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.