Check for valid file before hyperlinking

[Tommy Boy]

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]

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]

Hi Dave,

Thanks! I was able to get the macro below to work. I don't understand how,
or why it works, (wish I did) but it works. What's interesting though is
that I can't get it to find other file types by changing the file extension
in the code. I thought I could just change ".pdf" to ".doc" and hyperlink to
a doc file in the same directory. Is there a simple reason this doesn't work
this way?

Anyway, thanks again!!

Sub TestHyperlink()

ActiveCell.Select
Dim TestStr As String
Dim myFileName As String
myFileName = "C:\Folders\Crit Scans\" & ActiveCell.Offset(0, 2).Value _
& " - " & ActiveCell.Value & ".pdf"
TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0
If TestStr = "" Then
MsgBox "No Matching File To Link"
Else
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"C:\Folders\Crit Scans\" & ActiveCell.Offset(0, 2).Value _
& " - " & ActiveCell.Value & ".pdf", TextToDisplay:= _
ActiveCell.Value
ActiveCell.Select
With Selection.Font
.Name = "Arial"
.Size = 12
End With
ActiveCell.Offset(1, 0).Range("A1").Select
End If
End Sub

 

[Dave Peterson]

I would have guessed that changing the extension to .doc would be sufficient,
too.

I'm betting that there's a difference in the name of the file (small typo??)
that breaks it.

But that's just a guess. You didn't actually say that what broke--adding the
hyperlink or clicking on it????

And if I'm guessing wrong, what's the name of the .doc file? And what are the
values in those cells when you run the code?

A line like:
msgbox "***" & myfilename & "***"
may be useful to help find the problem.

 

[Tommy Boy]

Hi Dave,

It turns out I Ultimately had a typo. I was able to figure it out and get
it all to work.

Thanks for your help!!