I have a requirement to insert hyperlinks to .jpg files located in a particular folder on my hard drive, but I only want the hyperlink to be inserted if the file exists. The hyperlinks are to be inserted into several hundred row of a particular column, and each image name is unique.
After searching the internet for some time, the best solution I have found is to create a UDF function that accepts the filename and path as and argument and returns TRUE if the file exists or FALSE if it does not.
This UDF is:
Private Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x <> "" Then FileExists = True _
Else FileExists = False
I then simply called the function and insert a link to the image if it exists:
IF(fileexists(C:\path\image x.jpg),(HYPERLINK(C:\path\image x.jpg)),"")
OK so this sort of works if I enter the formula into each cell, but I need to copy this formula down hundreds of rows of a single column, and when I do this the formula won't seem to run on each row, even if the file exists.
I've done some searching on this problem too, and it people have been suggesting inserting Application.Volatile at the beginning of the function, but I'm reluctant to do this unless its really required.
Does anyone have any suggestions to resolve my refresh issue with this UDF, or does anyone have another solution to my requirement?
Hopefully I've included all of the key information.