Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • 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

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

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