View original (French)

Bulk hyperlink modification in Excel

thijama67 Posted messages 10 Status Member -  
cousinhub29 Posted messages 1113 Registration date   Status Member Last intervention   -

Hello, following a modification (adding a directory in a structure), my hyperlinks in my Excel spreadsheet no longer work because they open a file that has changed locations.

I am very new to macros, I’ve tried many things and I must admit that I’m struggling a bit (actually, a lot!!)!

Here’s my old hyperlink where my files were stored:

C:\Users\XXXX\SynologyDrive\ZZ-AAAA\Chrono admin et tech\Chrono admin\" . D

Here’s my new hyperlink where my files are now stored:

C:\Users\XXXX\SynologyDrive\ZZ-AAAA\Chrono admin et tech\Chrono admin\2022". My files are now all in this new "2022" directory, in fact, I sorted my files by year to distinguish between 2021 and 2022

I have about 5000 hyperlinks to modify, could you please guide me on a way to change each link so that when I click on it, it opens correctly without having to modify them one by one.

Thank you very much for your help.

Yours sincerely

Stéphane


5 answers

  1.
    The_boss_68 Posted messages 959 Registration date   Status Member Last intervention   182
     

    Good evening,

    Maybe take a look at this link HERE

    Regards

    0
  2. thijama67 Posted messages 10 Status Member
     

    Good evening, thank you for this link. It works, however, when modifying it, it changes all the links in the other sheets of the workbook.

    I had already tried in vain.

    I was rather inclined towards a macro, but I must admit I don't master macros at all.

    0
  3. yclik Posted messages 3875 Registration date   Status Member Last intervention   1 610
     

    Hello

    try using Find/Replace

    find

    Chrono admin\

    replace with

    Chrono admin\2022

    0
  4. thijama67 Posted messages 10 Status Member
     

    Hello, thank you for this information. I tried it, but the find and replace function does not read within the hyperlink and therefore doesn't find the term to change.

    .

    0
  6. cousinhub29 Posted messages 1113 Registration date   Status Member Last intervention   383
     

    Hello,

    Try this:

    - Open the VBA editor (Alt + F11)

    - Insert/Module

    - Paste this code into the new module:

     Sub modif_liens() Dim Hpl As Hyperlink Dim Old_Chm As String, New_Chm As String 'Old path and New path Old_Chm = "Chrono admin\": New_Chm = "Chrono admin\2022\" For Each Hpl In Sheets("Feuil1").Cells.Hyperlinks 'Watch out for the sheet name Hpl.Address = Replace(Hpl.Address, Old_Chm, New_Chm) Next Hpl End Sub

    Then, after clicking in the middle of the code, press F8 (step mode) and F5 (run all the code)

    Do a test on a copy, of course

    Good luck

    0