Solved

Excel 2010 UDF to insert hyperlink only if a file exists

Posted on 2011-03-02
6
490 Views
Last Modified: 2012-05-11
Hi,

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
End Function

Open in new window


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)),"")

Open in new window


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.

Many thanks,

stokenator
0
Comment
Question by:stokenator
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35024308
May I see a sample file? Just five rows of info will do so that I get my references correct.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35024368
Since I had some free time I created a sample for you. Please replace the file names in Col A and then run the sub InsertHyperlinks() in module 1.

Sid

Code Used

Sub InsertHyperlinks()
    Dim i As Long, LastRow As Long
    Dim StrTemp As String
    
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To LastRow
        If FileExists(Sheets("Sheet1").Range("A" & i).Value) Then
            StrTemp = Sheets("Sheet1").Range("A" & i).Value
            Sheets("Sheet1").Hyperlinks.Add Anchor:=Range("A" & i), Address:=StrTemp, _
            TextToDisplay:=StrTemp
        End If
    Next i
End Sub

Private Function FileExists(fname) As Boolean
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True
End Function

Open in new window

Inserting-Hyperlinks.xls
0
 

Author Comment

by:stokenator
ID: 35028919
Hi Sid,

Thanks for the quick response and for your help. Your solution works as describes but I'm having trouble adapting it to my needs, and I think its because I was not clear in my original post.

I actually have several columns where I need to do the same. I apologise for not mentioning this, but I was anticipating a formula that calls the FileExists UDF, therefore I thought I could use this formula where I needed it. To avoid an further confusion I have created an attached a file that (hopefully) clearly states what I need to do.

Essentially look for the file specified in columns G-J, and if they exist, paste hyperlinks to them in columns A-D, if not leave the cells blank.

Is it possible to have this function run each time the workbook is opened? I noticed with your function once the hyperlink is inserted, and I delete the file and rerun the code, the hyperlink remains.

Many thanks in advance,

stokenator


my-insert-hyperlink-sample-file.xlsx
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
ID: 35029187
Is this what you want?

I have attached 2 samples.

1) Manual. So that you can manually test it
2) Auto: Which will run automatically when you open the workbook.

Sid

1)
Code Used for manual in a module

Sub InsertHyperlinks()
    Dim i As Long, j As Long, LastRow As Long
    Dim StrTemp As String
    
    LastRow = Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
    lastcol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

    For i = 2 To LastRow
        For j = 7 To lastcol
            StrTemp = Sheets("Sheet1").Cells(i, j).Value
            If FileExists(StrTemp) Then
                Sheets("Sheet1").Hyperlinks.Add Anchor:=Sheets("Sheet1").Cells(i, j - 6), Address:=StrTemp, _
                TextToDisplay:=StrTemp
            Else
                Sheets("Sheet1").Cells(i, j - 6).ClearContents
            End If
        Next j
    Next i
End Sub

Public Function FileExists(fname) As Boolean
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True
End Function

Open in new window


2)
Code Used for Auto In Workbook Open Event

Private Sub Workbook_Open()
    Dim i As Long, j As Long, LastRow As Long
    Dim StrTemp As String
    
    LastRow = Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
    lastcol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

    For i = 2 To LastRow
        For j = 7 To lastcol
            StrTemp = Sheets("Sheet1").Cells(i, j).Value
            If FileExists(StrTemp) Then
                Sheets("Sheet1").Hyperlinks.Add Anchor:=Sheets("Sheet1").Cells(i, j - 6), Address:=StrTemp, _
                TextToDisplay:=StrTemp
            Else
                Sheets("Sheet1").Cells(i, j - 6).ClearContents
            End If
        Next j
    Next i
End Sub

Open in new window


Code Used for Auto In Module

Public Function FileExists(fname) As Boolean
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True
End Function

Open in new window

my-insert-hyperlink-sample-file-.xlsm
my-insert-hyperlink-sample-file-.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35029199
BTW I forget to dim lastcol

Please Add

Dim lastcol as Long to the above code.

Sid
0
 

Author Closing Comment

by:stokenator
ID: 35036875
Thanks very much Sid, that works great
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question