Forum Discussion

Jochoa1964's avatar
Jochoa1964
Copper Contributor
Oct 24, 2021
Solved

Changing multiple links to other worksheets - Help

Hi- I have a workbook that links to multiple workbook in diferent locations.

I need to change many files from locations but I would like to change massively not going one by one.

For example:

I have the following route to many files 'G:\2. Take Over 2\Profesional\Omma\Prácticas\8. Retorno Absoluto\Fondos\

And I want to change them to: G:\RF\

Do you know how can I do it? Thank you for your help!

excel
  • NikolinoDE's avatar
    NikolinoDE
    Oct 25, 2021

    Jochoa1964 

    Hope I got it right 🙂

    You want to change the link path on multiple hyperlinks ... if so then you could do it with VBA.

     

    Sub ReplaceallHyperlink()
    Dim oldPath As String
    Dim newPath As String
    Dim myLink As Hyperlink
   
    ' Attention: Pay attention to upper / lower case, is important for the replacement 
   oldPath = "C:\Users\nikolino\Downloads\Orga\"
   newPath = "C:\Users\nikolino\OneDrive\niko\Orga\Description\Quotation\"
   
    ' run through all hyperlinks in the sheet, sheet name must be adapted
    For Each myLink In Sheets("NikolinoDE").Hyperlinks

        ' and swap the old part for the new part in the address of the link 
 myLink.Address = Replace(myLink.Address, oldPath, newPath)
   
    Next
End Sub

     

    Hope I was able to help you with this info.

     

    I would be happy to know if I could help.

     

    NikolinoDE

    I know I don't know anything (Socrates)

     

    Was the answer useful? Mark them as helpful!

    This will help all forum participants.

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 25, 2021

    Jochoa1964 

    Hope I got it right 🙂

    You want to change the link path on multiple hyperlinks ... if so then you could do it with VBA.

     

    Sub ReplaceallHyperlink()
    Dim oldPath As String
    Dim newPath As String
    Dim myLink As Hyperlink
   
    ' Attention: Pay attention to upper / lower case, is important for the replacement 
   oldPath = "C:\Users\nikolino\Downloads\Orga\"
   newPath = "C:\Users\nikolino\OneDrive\niko\Orga\Description\Quotation\"
   
    ' run through all hyperlinks in the sheet, sheet name must be adapted
    For Each myLink In Sheets("NikolinoDE").Hyperlinks

        ' and swap the old part for the new part in the address of the link 
 myLink.Address = Replace(myLink.Address, oldPath, newPath)
   
    Next
End Sub

     

    Hope I was able to help you with this info.

     

    I would be happy to know if I could help.

     

    NikolinoDE

    I know I don't know anything (Socrates)

     

    Was the answer useful? Mark them as helpful!

    This will help all forum participants.

    • Jochoa1964's avatar
      Jochoa1964
      Copper Contributor
      Oct 26, 2021

      NikolinoDE Thank you!

      I will try it later

      So far I have done it with find and replace changing the path. However your way looks much more clean.

      I will revert.

      Thank you again.

Resources