Excel VBA to show hyper link only if a file exists

SimonPrice33
SimonPrice33 used Ask the Experts™
on
Hi,

In someones infinite wisdom they have asked me to program in VBA through an excel spreadsheet rather than let me code in VB.NET.

My boss wants me to show a hyper link to a file in an excel cell, only if the file exists...

I have found some code that will show a message box if the file exists or not (which works and shown below).

When I use the code Sub Test2 () on its own this also creates a hyper link,

But when i want to try and combine the codes this simply willl not work and throws me errors...

Any help would be greatfully received...



Option Explicit

Sub charter()

If Dir("c:\temp\debug.txt") <> "" Then
MsgBox "File exists"

Else
MsgBox "File does not exist"
End If

End Sub

Open in new window

Sub Test2()

ActiveSheet.Range("A19").Select
With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:= _
"c:\temp\debug.txt", TextToDisplay:="Test"
End With
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Pro/Developer
Top Expert 2008
Commented:
You can do this in the Workbook Open event
Private Sub Workbook_Open()
Dim fileName As String

    fileName = "d:\test.xls"
    
    If Dir(fileName) <> "" Then
        Sheet1.Hyperlinks.Add Anchor:=Range("A1"), _
        Address:=fileName, TextToDisplay:="Test"
    Else
        Range("A1").Select
        Selection.Hyperlinks.Delete
        Selection = ""
    End If
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial