I'd include the path in the test and hyperlink.

Dim TestStr as string
dim myFileName as string

myfilename = "C:\somepath\crit scans\" & activecell.offset(0,2).value _
& " - " & activcell.value & ".pdf"

teststr = ""
on error resume next
teststr = dir(myfilename)
on error goto 0

if teststr = "" then
msgbox "not a valid filename"
else
'do the hyperlink
end if

This won't help if the file is renamed/deleted after the hyperlink has been
created, though.

Tommy Boy wrote:

I have the following simple code to create a hyperlink in the existing cell
that links to a PDF file on my drive. The file name it uses is based on the
text shown in 2 different cells with " - " in between the two text strings.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"Crit%20Scans/" & ActiveCell.Offset(0, 2).Value & " - " &
ActiveCell.Value & ".pdf", TextToDisplay:= _
ActiveCell.Value

The problem is, if there is a typo in my cells or a file with that name
doesn't exist on the drive, the hyperlink is still created, but the link is
ultimately invalid. I have no way of knowing that it is invalid without
going back to check each link.

Is there a way that I can check to see if the pdf file exists before
creating a hyperlink. If it's not valid, it can simply move to the next cell
without creating a link; it doesn't have to be anything fancy.

I've seen "if - then" type suggestions here for various questions, however,
I'm relatively new to using VBA in Excel, so I'm not exactly sure if this
would work or how to properly insert them into a macro.

Thanks for any help!

--

Dave Peterson