View original (French)

Bulk hyperlink modification in Excel

thijama67 -  

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

    Good evening,

    Maybe take a look at this link HERE

    Regards

    0
    1. thijama67 Author

      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
      1. yclik

        Hello

        try using Find/Replace

        find

        Chrono admin\

        replace with

        Chrono admin\2022

        0
        1. thijama67 Author

          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
          2. cousinhub29

            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