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

Does the hyperlink exist?
 

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" wrote in message
...
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

Does the hyperlink exist?
 

"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