Check if file extsts before continue

[ipbr21054]

Morning,
I am using the code below but would like to check if file already exists.
Basically if file exists stop & show Msgbox advising user
If the file does not exist then continue


Currently nothing is in place & is just written over each time

Private Sub HyperlinkDiscoII_Click()
Dim wb As Workbook
Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
        If ActiveCell.Column = Columns("B").Column Then
          
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
        With ActiveCell
        .Font.Size = 12
        End With
        MsgBox "HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        
        End If
        
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE FILE.", vbCritical, "POSTAGE SHEET DISCO II HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".pdf") = "" Then
        If MsgBox("THERE IS NO FILE FOR THIS CUSTOMER" & vbNewLine & "WOULD YOU LIKE TO OPEN THE DISCO II FOLDER ?", vbYesNo + vbCritical, "HYPERLINK CUSTOMER DISCO II MESSAGE.") = vbYes Then
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\")
        
    End If
    End If
       Unload DiscoIIHyperlink
       Workbooks("DISCO CALC.xlsm").Close SaveChanges:=True
    
End Sub

 

[bobsan42]

see if this helps:

Function fileExists(strfilename As String) As Boolean
    'Requires reference to Microsoft Scripting Runtime
    Dim fso As New FileSystemObject
    fileExists = fso.fileExists(strfilename)
    Set fso = Nothing
End Function

or the Late binding version:

Function fileExists(strfilename As String) As Boolean
    With CreateObject("Scripting.FileSystemObject")
        fileExists = .fileExists(strfilename)
    End With
End Function

 

[RoryA]

I am using the code below but would like to check if file already exists

That code already does that with Dir.

 

[ipbr21054]

That code already does that with Dir.

Hmmm

I can mouse over to that cell & run the code & i see HYPERLINK SUCCESSFUL etc.
I dont get any message saying file exists or the like hence why i started this post

 

[bobsan42]

I dont get any message saying file exists or the like hence why i started this post

what means is that an actual check(s) for the existence of a file is performed:

Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf"))

the result will be a number > 0 if the file exists

Dir(FILE_PATH & ActiveCell.Value & ".pdf") = ""

this will return TRUE if the the file does not exist.

How you use this results in your code is a separate matter.

And in your code I actually see no file writing.

 

[ipbr21054]

Well im now confused.
The code i supplied hyperlinks a pdf file.

So i select the customer on the worksheet say TOM JONES 001
The code is run.
I see the message Hyperlink Successful.
Now the customers name is Blue & underlined.
Clicking on it will open the pdf file in the folder where they are stored.

My goal was should a user select or mouse to that cell where TOM JONE 001 is located & code is run THEN advise user.

Did i word it incorrectly & should of said check to see if the pdf is already hyperlinked ?

Sorry