Hyperlinks aangemaakt met de hyperlink formule checken als ze werken

[wtco]

Ik heb een excelfile met links naar duizenden foto's op mijn harddisk.
Deze links maak ik via de formule Hyperlink().

Maar een groot aantal van deze links werken niet als ik erop klik omdat de file soms niet bestaat.

Is er een manier via een macro om dit te detecteren:
Dus ik duid de range aan met de links en dan start ik via ALT F8 de betreffende macro module op en als uitkomst bekom je dat de links waarvan het "opgegeven bestand niet kan worden geopend" vb geel kleuren ?

Alvast bedankt voor de hulp want momenteel test ik ze één voor één en voel me als een monnik van één of andere trappistenabdij in de middeleeuwen

 

[popipipo]

Heb je misschien iets aan deze file?
Heb deze ooit eens gevonden op het www.

Vind hem zelf wel heel handig

 

[E v R]

- Haal middels VBA het pad naar het bestand uit de hyperlinkformule en gebruik dan een Dir Methode om te kijken of dit pad bestaat, zo nee dan ...

 

[wtco]

Ik heb wel een VBA module maar werkt niet als je links maakt via de hyperlink functie

Ik heb wel een VBA module maar werkt niet als je links maakt via de hyperlink functie.
Ik ben echter GEEN VBA expert zoals jullie. Misschien weet iemand raad om deze wat aan te passen voor paden+filenamen aangemaakt via de Hyperlink functie van excel ?

alle hulp is welkom !

Sub TestHLinkValidity()
Dim rRng As Range
Dim fsoFSO As Object
Dim strFullPath As String

Dim strPath As String
Dim strFName As String
Dim cCell As Range
Dim response
Dim intSlashCount As Integer

Set fsoFSO = CreateObject("Scripting.FileSystemObject")
Set rRng = Selection
For Each cCell In rRng.Cells
    If cCell.Hyperlinks.Count > 0 Then
        strPath = GetHlinkAddr(cCell)
        If fsoFSO.FileExists(strPath) = False Then
            cCell.Interior.Color = 65535
        End If
   End If
Next cCell
End Sub

Function GetHlinkAddr(rngHlinkCell As Range)
    GetHlinkAddr = rngHlinkCell.Hyperlinks(1).Address
End Function

 

[E v R]

plaats code svp tussen codetags,

Jouw code lichtjes gewijzigd:

Sub TestHLinkValidity()
 Dim rRng As Range
 Dim fsoFSO As Object
 Dim strFullPath As String

 Dim strPath As String
 Dim strFName As String
 Dim cCell As Range
 Dim response
 Dim intSlashCount As Integer

 Set fsoFSO = CreateObject("Scripting.FileSystemObject")
 Set rRng = Selection
 For Each cCell In rRng.Cells
 If cCell.HasFormula Then
 strPath = GetHlinkAddr(cCell)
 If fsoFSO.FileExists(strPath) = False Then
 cCell.Interior.Color = 65535
 End If
 End If
 Next cCell
 End Sub

 Function GetHlinkAddr(rngHlinkCell As Range)
 GetHlinkAddr = Split(rngHlinkCell.Formula, """")(1)
 End Function

 

[wtco]

Beste Eric,

bedankt voor de wijziging maar alles wordt met uw macro geel gekleurd. Ook de cellen met hyperlinks naar foto's die wel werken worden geel gekleurd.

Als ik de cellen met de hyperlink formule in de vorm =hyperlink(met het volledige pad + filenaam) vb =HYPERLINK("C:\Users\Publicook\Pictures\Sample Pictures\aap.jpg")
test dan werkt uw macro wel.

Maar als ik het pad samenstel met een formule =HYPERLINK(TEKST.SAMENVOEGEN("C:\Users\Public\Pictures\Sample Pictures";"aap.jpg")) dan werkt uw macro niet meer de cel wordt altijd geel gekleurd ook al werkt de hyperlink wel

P.S. uiteraard is het pad in mijn excelblad altijd C:\Users\Public\Pictures\Sample Pictures\ en verandert de filenaam hier voor de lol aap genomen steeds via de input van andere cellen

Kan je een voorbeeldexceltje met uw macro doorsturen zodat ik mijn formule met mijn hyperlinks eens kan testen hoe het komt dat uw macro niet werkt ?

Nogmaals bedankt voor de hulp maar er is blijkbaar reeds op veel fora ook in America over testen van hyperlinks geschreven maar nog niemand heeft een duidelijke oplossing gevonden.

 

[E v R]

Zullen we dat andersom doen?

Plaats eens een vb-tje met een paar van die hyperlink-formules onder elkaar,

Gokje: in de Hyperlinkformule verwijs je naar andere cel(len) waar de link staat?

 

[E v R]

Kijk nu naar jouw edit, geef je al aan dat de link samengesteld wordt,

Kort door de bocht pas de code vwb de funktie eens zo aan:

 Function GetHlinkAddr(rngHlinkCell As Range)
 Dim x
 x = Split(rngHlinkCell.Formula, """")
 GetHlinkAddr = x(1) & "\" & x(3)
 End Function

ervanuitgaande dat jouw opbouw altijd zo is:

=HYPERLINK(TEKST.SAMENVOEGEN("C:\Users\Public\Pictures\Sample Pictures";"aap.jpg"))


ps

maar als die aap.jpg uit een cel komt hoef je het ook niet uit de hyperlink-formule te distelleren.

Het gaat erom dat je steeds de volledige link in GetHlinkAddr krijgt en dan werkt de bovengenoemde code prima

maar een gelijkend voorbeeldje had het aantal posts in dit draadje al tenminste gehalveerd en zou jij een werkende code hebben, blijft gissen zo...

 

[wtco]

Bekijk bijlage voorbeeldje voor Eric.xlsm

Beste Eric,
in bijlage het gevraagde gemaakte voorbeeldje en een zelfportret van mij om te testen...

Je was me reeds te vroeg af met uw aanpassing "kort door de bocht". Let wel ik ben geen programmeur. Ken in feite niks af van VBA.

groeten,
Filiep

 

[E v R]

Zit nu in auto, kan er morgen pas naar kijken, wellicht een andere helper eerder

 

[wtco]

Beter concreet voorbeeld excel om de macro "custom made" op te schrijven

Beste Eric,

het kan best wachten tot morgen.
Ik was er mij niet bewust van dat om de hyperlinks te testen, de manier waarop deze worden samengesteld zo belangrijk is.
Het blijkt dat de macro "custom made" moet zijn.

Vandaar dat ik een beter voorbeeldje heb gemaakt met meteen 4 foto's, die ik ergens heb geplukt, met de namen uit de sheet die zouden moeten werken.
Belangrijk is dat de excel precies de manier bevat waarop ik de hyperlinks heb samengesteld.
Dus vergeet het voorbeeldje met de aap dat ik snel had gemaakt om uw laatste macro te testen.

In bijlage enkele foto's en een beter voorbeeld om de macro in orde te maken. Zo is het meer to the point. Maar zoals gezegd dacht ik dat een professioneel geschreven macro voor alle soorten hyperlinks zou werken zowel naar een bestand op een harde schijf of naar een externe server op het net.

alvast bedankt voor uw tijd en moeite !!

 

[E v R]

selecteer B6 tm d12

en draai deze licht aangepaste macro:
(met de volledige link zichtbaar in de cel en bestanden op HD)

Sub TestHLinkValidity()
 Dim rRng As Range
 Dim fsoFSO As Object
 Dim strPath As String
 Dim cCell As Range
 
 Set fsoFSO = CreateObject("Scripting.FileSystemObject")
 Set rRng = Selection
    For Each cCell In rRng.Cells
        If cCell.HasFormula Then
            strPath = cCell.Text
            If fsoFSO.FileExists(strPath) = False Then
                cCell.Interior.Color = 65535
            Else
                cCell.Interior.Color = xlNone
            End If
        End If
    Next cCell
 End Sub

 

[wtco]

Hallo Eric,

ik heb het getest op mijn voorbeeldsheet die ik hiervoor heb gepost en het werkt perfect !
Onmiddellijk getest op mijn megafile thuis en... het werkt ook perfect !! Alle cellen waarvan de hyperlink niet werkt kleuren geel !

Ik wil niet overdrijven en blijven vragen - ben zo reeds :thumb: - maar al mijn hyperlinks die niet bruikbaar zijn vertragen mijn megasheet dusdanig zodat ik ze graag zou verwijderen en liefst automatisch want het zijn er een paar duizend.

Is het mogelijk uw module aan te passen zodat de cellen niet geel kleuren maar dan "gewoon" worden gewist of er een spatie wordt ingeschreven. Dus cCell.Interior.Color= vervangen door een commando dat de cel wist.


Zoals gemeld ken ik helemaal niets af van VB. Heb je eventueel ook ergens een link naar een goede basiscursus VBA dan kan ik me wat basis commandos eigen maken want momenteel voel ik me net de aap uit de foto die ik hierboven doorgestuurd heb en moet ik u zelfs vragen hoe een cel te wissen wat allicht een basiscommando is

alvast bedankt voor uw hulp !

 

[E v R]

ipv

cCell.Interior.Color = 65535

 cCell.ClearContents

 

[wtco]

hallo Eric,

nogmaals bedankt.
Zoals gemeld ken ik helemaal niets af van VB. Heb je eventueel ook ergens een link naar een goede basiscursus VBA dan kan ik me wat basis commandos eigen maken.

Want het is beschamend dat ik moet vragen hoe een cel te wissen: cCell.ClearContents

 

[E v R]

Geen probleem, weet zo niet 1 2 3 waar je een goede VBA basis cursus kan vinden, qua VBA (instap) boeken vind ik ze van John Walkenbach wel OK

 

[wtco]

Ja de Excel VBA Programming For Dummies zal iets voor mij zijn.

Beste Eric ik wil u nogmaals uitdrukkelijk bedanken voor uw hulp en moeite.
Het is ongelofelijk dat jullie dit belangeloos doen.

Ik zal deze thread markeren als opgelost

vele groeten,
Filiep