External File Validation?

[Guest]

I received some assistance on putting this macro together and tweaked it
until it did what I wanted. However, I had to change the name of the input
file and realized that if the file name the script searches for is not valid,
it will kick out an error.

I want to be able to include a statement that says something like, IF
FILENAME.xls IS MISSING THEN "FILE MISSING" or something like that.

How would I modify this code?

Sub SourceDateTime_VENDOR_A()
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("FILEPATH_GOES_HERE\FileName.txt")
Application.Goto Reference:="R18C1"
ActiveCell.FormulaR1C1 = (f.datelastmodified)
Application.Goto Reference:="R18C7"
ActiveCell.FormulaR1C1 = "=IF(RC[-6]>(TODAY()+0.99999),""Future
Data?"",(IF(RC[-6]<TODAY(),""Old Data"",""Validated"")))"
End Sub

Thanks for your help!

 

[George Nicholson]

The Dir function will return an empty string ("") if a specified filename
does not exist.

strtFilename = "FILEPATH_GOES_HERE\FileName.txt"
If Len(Dir(strFilename))=0 Then
'File doesn't exist. Exit? prompt User?
Msgbox "Cancelling: " & strFilename & " does not exist."
Exit Sub
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(strFileName)
'........etc

HTH,