View original (French)

Bulk hyperlink modification in Excel

thijama67 Posted messages 10 Status Membre -  
cousinhub29 Posted messages 1112 Registration date   Status Membre 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 réponses

Réponse 1 / 5
The_boss_68 Posted messages 959 Registration date   Status Membre Last intervention   182
 

Good evening,

Maybe take a look at this link HERE

Regards
0
Réponse 2 / 5
thijama67 Posted messages 10 Status Membre
 

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
Réponse 3 / 5
yclik Posted messages 68 Registration date   Status Membre Last intervention   1 604
 

Hello

try using Find/Replace

find

Chrono admin\

replace with

Chrono admin\2022
0
Réponse 4 / 5
thijama67 Posted messages 10 Status Membre
 

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
Réponse 5 / 5
cousinhub29 Posted messages 1112 Registration date   Status Membre 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