Search code examples
excelvba

Create hyperlink for image in a folder based on a cell value

I'm working on a sheet containing the employees data with a serial number in cell "B5:B500" and I have an ID image for each employee in a folder on my pc named with the same serial number of the employee in Column ("B") now I need to create an automatic hyperlink for the employee's ID image in column "V") based on the cell value in Column "B" whenever I add a new employee number *** here is the folder path ("D:\Desktop\Guards\Guards National IDs").

Now I have this function working perfectly but only for JPG file format and all what i need is to make it includes png,jpg & jpeg format

=HYPERLINK("D:\Desktop\Guards\Guards National IDs\" & $B5 & ".jpg", $B5)

Solution

  • You can get around the variation in extensions of your image files like this. Lets say this is your worksheet structure:

    Worksheet example

    Lets say your image file names are the same as the IDs I have in Column A in the screenshot above (so 001.png, 002.jpg, and so on):

    image file names

    Then, if you put the code I posted for the custom function called imgPath() below into a Module like this:

    Creating a module

    Function in module

    Function imgPath(path, imgNum As String)
    Dim imgNameWithExtension As String
    imgNameWithExtension = Dir(path & imgNum & ".*") 'Using multiple wildcards character (*) to get any extension.
    If imgNameWithExtension <> "" Then
        imgPath = path & imgNameWithExtension
    Else
        imgPath = CVErr(xlErrName) 'Dir() returns a zero-length string ("") if file wasn't found, in which case imgPath() returns an error.
    End If
End Function

    And change the Hyperlink formulas in your worksheet to this format:

    =HYPERLINK(imgPath("D:\Desktop\Guards\Guards National IDs\", A2), A2)

    Worksheet formulas view Worksheet view2

    You'll then get hyperlinks to the files that could be found, regardless of their extensions, but #errors for files that couldn't be found (to help troubleshoot). Clicking on valid hyperlinks opens up the associated files: in my case link 001 opens 001.png, and link 002 opens 002.jpg, but no files are opened in the cells with #errors.

    Edit: If you'd like to store values in the ID/Column A as numeric/general as opposed to text (as I have in my example); in other words, your Column A values will be 1, 2, 3... instead of '001, '002... Then you could use this modified format for the Hyperlink formula (while retaining the file naming convention of 001.png, 002.jpg...):

    =HYPERLINK(imgPath("D:\Desktop\Guards\Guards National IDs\", TEXT(A2,"000")), A2)