Solved

Excel 2010 - modify file link paths in VBA

Posted on 2015-01-06
9
3,552 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 51

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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 51

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 51

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
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…

734 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