checking file exists before adding hyperlink

I have the following code:

Sub attachyperlinks()

Dim MyCell As Object
Dim Link As String


For Each MyCell In Selection
    If MyCell <> "" Then
        ActiveSheet.Hyperlinks.Add Anchor:=MyCell, Address:="C:\Users\folderA\folderB\log\" & MyCell.Value & ".pdf", ScreenTip:="Click to Open"
    End If

Next MyCell

MsgBox ("Hyperlinks attached successfully.")

End Sub

Which attaches a hyperlink to the selected cells using cell contents are filename.

What i now need to add is code to make it check if the file exists first before adding the hyperlink. If the file is not there then the hyperlink should not be added ie leave cell contents as is.

Can someone please advise me?

hi,

i have tried both iffileexists and the DIR functions but cant quite get it to work.

Sub Checkfileandhyperlink()

Dim MyCell As Object
Dim Link As String
Dim filecheck As Boolean

For Each MyCell In Selection
    If MyCell <> "" Then
    If Dir("C:\Users\" & MyCell.Value & ".pdf") <> "" Then
       filecheck = True
    Else
        filecheck = False
    End If
    End If

   If filecheck = True Then
        ActiveSheet.Hyperlinks.Add Anchor:=MyCell, Address:="C:\Users\" & MyCell.Value & ".pdf", ScreenTip:="Click to open."
    Else
    End If

Next MyCell

End Sub

The contents of the cells are just hyperlinked regardless of whether the file is present.

Any ideas of what i am doing wrong would be very much appreciated.

Many thanks,

Hi, TaliKL,

Sub Checkfileandhyperlink()

Dim MyCell As Object
Dim filecheck As Boolean

For Each MyCell In Selection
  If MyCell <> "" Then
    filecheck = Len(Dir("C:\Users\" & MyCell.Value & ".pdf")) > 0
  End If
  
  If filecheck Then
    ActiveSheet.Hyperlinks.Add Anchor:=MyCell, Address:="C:\Users\" & MyCell.Value & ".pdf", ScreenTip:="Click to open."
  End If

Next MyCell

End Sub

Ciao,
Holger

Hi, TaliKL,

you´re welcome. Thanks for adding the code-tags. If I remember correctly you may only delete a post if no answer has been issued to it (so it must be the opening post in a thread).

Hope you got the problem fully solved.

Ciao,
Holger

TaliKL,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

Hello, just joined the forum, 2nd time posting, please be easy...
I took a stab at trying to adapt this to create a hyperlink to a file that begins with a prefix established 1 cell to the left, and a path established 3 cells to the left.
Looks liks I'm getting an error trying to define Path and Prefix?
Also, probably skipped a couple steps to get here, but how do I run this script (basically want to create hyperlinks down the whole column)
Thank you!

Sub CreateHyperlinks()

Dim MyCell As Object
Dim Path As Object
Dim Prefix As Object
Dim filecheck As Boolean

For Each MyCell In Selection
    If MyCell <> "" Then
        Path = MyCell - 3
        Prefix = MyCell - 1
        filecheck = Len(Dir(Path & Prefix & "*.pdf")) > 0
    End If
    If filecheck Then
        ActiveSheet.Hyperlinks.Add Anchor:=MyCell, Address:=Dir(Path & MyCell & "*.pdf")
    End If

Next MyCell

End Sub

Hi, skalumba,

Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

Ciao,
Holger

Thank you Hahobe.

Skalumba,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.