Check for filename before placing hyperlink

Reset

Board Regular
Joined
Apr 16, 2010
Messages
227
I have a short macro that adds hyperlinks to files where the filename is the value in the first cell fro each row. It currently checks and adds if the target cell for the hyperlink is empty. But I would also like to check and make sure the filename exists in the target folder before adding the hyperlink as well. Here's the code so far:
Code:
Sub linkup()
Dim name As String
Dim c As Integer
Sheets("Sheet1").Select
c = WorksheetFunction.CountA(Columns(1))
' Filenames are in column 1, hyperlink goes is column 7
For x = 2 To c
    If Cells(x, 7) = "" Then
        name = Cells(x, 1)
        ActiveSheet.Hyperlinks.Add anchor:=Cells(x, 7), Address:= _
            "C:\Users\HumanBeing\Documents\" & name & ".pdf",  TextToDisplay:= _
            "PDS"
    End If
Next x
End Sub

Thanks.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
You can use Dir() to test if a file exists....

Code:
    Dim sFileSpec As String
    sFileSpec = "C:\Users\HumanBeing\Documents\" & Name & ".pdf"
    If Dir(sFileSpec) <> "" Then
        MsgBox sFileSpec & " exists"
    Else
        MsgBox sFileSpec & " doesn't exist"
    End If
 

Watch MrExcel Video

Forum statistics

Threads
1,123,108
Messages
5,599,760
Members
414,336
Latest member
Nicolas2465

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top