Modifying Hyperlink variable in VBA

[BungleNZ]

Hi all

I have created a list of about 3000 hyperlinks in a spreadsheet that links to pdf files on my computer. Some of the hyperlinks are not valid, due to there being an error in the file name. Browsing the net etc, I have got code to test all of the hyperlinks on the sheet to determine whether they are valid or not, but I am having problems in subsequently renaming the hyperlink to make it functional.

The base file name structure is 5 digits, followed by potentially 1 letter. I am trying to come up with code that will check the hyperlink variable, if the code is incorrect, it will add the next sequential letter before ".pdf" and retry the hyperlink again, and over again until the hyperlink is correct and then move onto the next hyperlink that is incorrect.

EG:
\\DIES\30000\30007\PDF\30007.pdf

If this hyperlink is invalid, try

\\DIES\30000\30007\PDF\30007a.pdf

then

\\DIES\30000\30007\PDF\30007b.pdf

Until the hyperlink is correct (or until it gets to Z, in which case, move on to next hyperlink).

Here's my code:

Sub ChkHypLnks()
Dim wksHypLnks
Dim curHypLnk As Hyperlink
Dim curFile As String
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Set wksHypLnks = ActiveSheet
<o:p></o:p>
For Each curHypLnk In wksHypLnks.Hyperlinks
If Dir(curHypLnk.Address) = "" Then
For hyp = A To Z
<o:p></o:p>
'Add code here to add variable hyp, before “.pdf” in hyperlink address, 
then retry hyperlink, trying next letter if a is not correct
 
<o:p></o:p>
Next hyp
End If
<o:p></o:p>
Next curHypLnk
<o:p></o:p>
End Sub

Thanks for any help anyone can provide

Jamie

 

[sweater_vests_rock]

hey jamie

try this:

Sub ChkHypLnks()
    
    Dim wksHypLnks As Worksheet
    Dim curHypLnk As Hyperlink
    Dim curFile As String
    Dim stAZ As String, stNewAdd As String
    
    Set wksHypLnks = ActiveSheet
    stAZ = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
    For Each curHypLnk In wksHypLnks.Hyperlinks
        
        If Dir(curHypLnk.Address) = "" Then
            stNewAdd = ""
            For hyp = 1 To 26
                
                stNewAdd = Left(curHypLnk.Address, Len(curHypLnk.Address) - Len(".pdf")) & Mid(stAZ, hyp, 1) & ".pdf"
                If Dir(stNewAdd) <> "" Then
                    curHypLnk.Address = stNewAdd
                    Exit For
                End If
            Next hyp
        End If

    Next curHypLnk

End Sub

note, it will only work with PDF extensions...
ben.

 

[BungleNZ]

That works great thanks Ben!