Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

excel links

Hi,

is there any way to update the links in all excel links?

we have new DFS set up and most of the excel sheets have hard links that needs to updated with updated DFS path.

does Excel sheet needs to opened to update links?

I'm looking for a quick and easy solution please if any exists

thanks
0
kuzum
Asked:
kuzum
  • 3
1 Solution
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
here is a macro you can run to change links.  You will be prompted with the path and filename of each link. You can edit it, paste a new value, or skip it.
Sub ChangeWorkbookLinks()
'161111 strive4peace

   Dim vLinks As Variant
   Dim i As Integer
   Dim sNewLink As String
   
   vLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
   If Not IsEmpty(vLinks) Then
      For i = LBound(vLinks) To UBound(vLinks)
         sNewLink = InputBox("Enter new path\file for link", "Change Link", vLinks(i))
         If sNewLink <> "" And sNewLink <> vLinks(i) Then
            ActiveWorkbook.ChangeLink vLinks(i), sNewLink
         End If
      Next i
   End If
End Sub

Open in new window

0
 
kuzumAuthor Commented:
thanks for this.

do I need to use this for each excel and each excel needs to be opened?  OR you run this in one excel against all excel sheets in the directory?

regards
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome
This code would run on each workbook. To make it easier, put it in your personal macro workbook so you can run it whenever you want and on whatever workbook is active.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
solution provided by expert
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.

Join & Write a Comment

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now