Hyperlink Extension IF Statement

[stapuff]

I am looking to find a way for a formula to check what file extension is available before it creates the hyperlink. I an currently using this formula and specifying a .pdf but it could be a .doc, .tif, or .txt file extension.

=IF(BS3="","",HYPERLINK("S:\Master_Production\DEF\"&BO3&" Batch "&BS3&".pdf","View Test Document"))

Any thoughts would be greatly appreciated,

stapuff

 

[stapuff]

Well I have a solution...just not specifically the way I wanted it done.

Added this function to a module

Function FileExists(Filename As String) As Boolean
Application.Volatile
Set fs = CreateObject("Scripting.FileSystemObject")
FileExists = fs.FileExists(Filename)
Set fs = Nothing
End Function

then built a nested formula

=IF(G3="","",IF(FileExists("S:\Master_Production\DEF\"&C3&" Batch "&G3&".pdf")=TRUE,HYPERLINK("S:\Master_Production\DEF\"&C3&" Batch "&G3&".pdf","View Test Document"),IF(FileExists("S:\Master_Production\DEF\"&C3&" Batch "&G3&".doc")=TRUE,HYPERLINK("S:\Master_Production\DEF\"&C3&" Batch "&G3&".doc","View Test Document"),IF(FileExists("S:\Master_Production\DEF\"&C3&" Batch "&G3&".tif")=TRUE,HYPERLINK("S:\Master_Production\DEF\"&C3&" Batch "&G3&".tif","View Test Document"),""))))

Need to work on this one so more.

Stapuff