Solved

Excel 2010 - modify file link paths in VBA

Posted on 2015-01-06
9
2,663 Views
Last Modified: 2015-01-07
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
0
Comment
Question by:gpchicago08
  • 5
  • 3
9 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40533827
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40533880
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
 

Author Comment

by:gpchicago08
ID: 40533942
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
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40533953
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:gpchicago08
ID: 40533970
Sorry Rgonzo - still happening.  I wish I knew more VBA to help.  But it is still changing both file link paths
0
 

Author Comment

by:gpchicago08
ID: 40534205
Wait... I think it does work now.
0
 

Author Comment

by:gpchicago08
ID: 40535509
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
 

Author Closing Comment

by:gpchicago08
ID: 40535510
Thanks for the code assistance!  It worked.... just not on a protected worksheet.  Booooo.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40535529
Hi,

you can unprotect throught VBA

Sheets("Sheet1").Unprotect "myPW"   ' "myPW" if necessary
' your code
Sheets("Sheet1").Protect "myPW"
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about problems I had with the new location API and permissions in Marshmallow
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now