Meerdere hyperlinks aanpassen (VBA)

Geplaatst in VBA door Exhelp | Tags: , , , , , , ,
Van toepassing op: Excel 2003, Excel 2007, Excel 2010, Excel 2013

20150925_img

In Excel kan je hyperlinks invoegen naar externe locaties. Dit kan zowel naar websites als naar lokale bestanden. Heel erg handig, maar wat als je op een bepaald moment die hyperlinks wil wijzigen?

Onlangs had ik een bestand met enkele honderden hyperlinks. Na een crash van Excel, had ik de herstelde versie van mijn bestand opgeslagen. Gevolg: de hyperlinks waren allemaal gewijzigd naar een vreemd pad:

Het oude pad was:
C:\ExhelpRules\BIJLAGEN\exhelp*****.pdf

maar nu was het pad plots gewijzigd naar:
C:\Users\exhelp\Application Data\Microsoft\BIJLAGEN\exhelp*****.pdf

Op de plaats van de jokertekens stond een unieke code, die bij elke hyperlink verschillend was.

Zoeken en vervangen (CTRL+H)? Dat had je gedacht. Excel gaat niet zoeken in de locatie van je hyperlinks.

Natuurlijk had ik wel een back-up, maar ik had inmiddels weeral andere wijzigingen gemaakt in de laatst opgeslagen versie. Dat was dus al niet de ideale oplossing.

Gelukkig is er nog VBA…

ExhelpFindReplaceHyperlink

Het is heel eenvoudig. We schrijven gewoon een stukje code vergelijkbaar aan de functie zoeken/vervangen, maar dan voor hyperlinks.

Je kan onderstaande code gewoon plakken in een VBA module. Ga naar je werkblad waar de hyperlinks zich bevinden. Druk op ALT+F8, kies de macro en druk vervolgens op “uitvoeren”.

find_replace_hyperlink

Er verschijnt een pop-up voor de te zoeken waarde.  In mijn voorbeeld zou ik hier dan invullen:

C:\Users\exhelp\Application Data\Microsoft\BIJLAGEN\

Druk vervolgens op “OK”

20150925_1

Er verschijnt een pop-up voor waarde die je in de plaats wil zetten. In mijn voorbeeld zou ik hier dan invullen:

C:\ExhelpRules\BIJLAGEN\

Druk vervolgens op “OK”

20150925_2

Onze macro zal nu de hyperlinks aanpassen, en je achteraf een bericht geven van het aantal aanpassingen dat gebeurd is.

20150925_3

TIP! Je kan zowel een volledige hyperlink als een stuk van een hyperlink invullen. Je kan bijvoorbeeld dus ook ExhelpRules vervangen door ExhelpRocks, of .pdf vervangen door .jpg

VBA Code

Sub ExhelpFindReplaceHyperlink()

Dim s_find As String
Dim s_replace As String
Dim h As Hyperlink
Dim x As Integer
Dim c As Long

s_find = InputBox("Zoeken in hyperlink naar", "http://www.exhelp.be", "")
s_replace = InputBox("Vervangen in hyperlink door", "http://www.exhelp.be", "")

For Each h In ActiveSheet.Hyperlinks
    
    x = InStr(1, h.Address, s_find)
    
    If x > 0 Then
        
        If h.TextToDisplay = h.Address Then
            h.TextToDisplay = s_replace
        End If
    
        If s_find = "" Then GoTo ErrorHandler
    
        h.Address = Replace(h.Address, s_find, s_replace)
        
        c = c + 1
    
    End If

Next

MsgBox "Er werden " & c & " hyperlinks aangepast.", vbOKOnly, "http://www.exhelp.be"
       
Exit Sub
ErrorHandler:     MsgBox "Er werden 0 hyperlinks aangepast.", vbOKOnly, "http://www.exhelp.be"
       
End Sub

 

 

3 reacties op “Meerdere hyperlinks aanpassen (VBA)”

  • Waarom een worksheetfunction gebruiken als er een VBA-alternatief is ?
    h.Address = Replace(h.Address, s_find, s_replace)

    • Hé Warme Bakkertje. Bedankt voor je input.
      Waarom? Geen goede reden. Slechte gewoonte denk ik :-).

      Bij deze aangepast:

      h.Address = Application.WorksheetFunction. _
              Substitute(h.Address, s_find, s_replace)
      

      naar

      h.Address = Replace(h.Address, s_find, s_replace)
      
  • Bedankt. Als niet-VBA-expert was het even zoeken waarom niet al mijn hyperlinks werden vervangen. De macro gaat blijkbaar uit van een aaneengesloten bereik: in mijn bestand staan de hyperlinks verspreid over het werkblad (met tussenliggende legen cellen, kolommen, rijen) waardoor de macro aanvankelijk slechts enkele hyperlinks verving. Als workaround ff alle lege cellen gevuld met een x. Daarna werkt dit voor mij perfect.

Plaats een reactie

U moet ingelogd zijn om te reageren.

Login

Forums

Exhelp

Meest recente berichten

TAGS



Creative Commons Licentie
Op dit werk is een Creative
Commons Licentie
van toepassing.