Does the hyperlink exist?

I have a rake of files where the file name is in a spreadsheet column but the
file extension is not. There are only two possibilities .pdf or .tif.

How do I get the bit marked ** to work??

I can get the filename from the worksheet by a loop

Filename = Worksheets(SheetName).Cells(row_index, column_index).Value

What I want to do then is

vFile = filename & ".PDF"
vPath = "http:\\amsds0004\chelmsford\ECR_metrics\"

'If the file exists with a PDF extension then ...
** If exists vPath + vFile then
With Worksheets(SheetName)
.Hyperlinks.Add .Cells(row_index, column_index), vFile
End With
'Otherwise it must be a TIF extension so ...
Else
vFile = filename & ".TIF"
With Worksheets(SheetName)
.Hyperlinks.Add .Cells(row_index, column_index), vFile
End With
End if


Thanks Paul

Use the Dir function.

If Dir(vPath & vFile) <> "" Then
' file exists
Else
' file does not exist
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"BigPaul" <[email protected]> wrote in message
news:[email protected]...
>I have a rake of files where the file name is in a spreadsheet
>column but the
> file extension is not. There are only two possibilities .pdf
> or .tif.
>
> How do I get the bit marked ** to work??
>
> I can get the filename from the worksheet by a loop
>
> Filename = Worksheets(SheetName).Cells(row_index,
> column_index).Value
>
> What I want to do then is
>
> vFile = filename & ".PDF"
> vPath = "http:\\amsds0004\chelmsford\ECR_metrics\"
>
> 'If the file exists with a PDF extension then ...
> ** If exists vPath + vFile then
> With Worksheets(SheetName)
> .Hyperlinks.Add .Cells(row_index, column_index), vFile
> End With
> 'Otherwise it must be a TIF extension so ...
> Else
> vFile = filename & ".TIF"
> With Worksheets(SheetName)
> .Hyperlinks.Add .Cells(row_index, column_index), vFile
> End With
> End if
>
>
> Thanks Paul
>

"Chip Pearson" wrote:

> Use the Dir function.
>
> If Dir(vPath & vFile) <> "" Then
> ' file exists
> Else
> ' file does not exist
> End If
>
>
> --
> Cordially,
> Chip Pearson

Thanks Chip ~ a much better solution than my rather clunky. I'll use your
version.
Paul

With Application.FileSearch
.NewSearch
.LookIn = vPath
.SearchSubFolders = False
.FileName = vFile
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles

FileFound = (.Execute() > 0) 'True if found, false if not.
End With