Solved

Excel 2010 - modify file link paths in VBA

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

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 50

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 50

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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…

765 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