Solved

Excel 2010 - modify file link paths in VBA

Posted on 2015-01-06
9
3,806 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 26

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 52

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 52

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
 

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 52

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Make the most of your online learning experience.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

630 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