EXCEL HYPERLINK: Link JPG in folder via hyperlink

Question

I am trying to figure out if I can link a .jpg file with an Excel via hyperlink (or in another way).

Let me explain a bit more what I an trying to do:

Using Excel 2013:

1. I have a folder with +1000 images, named by EAN code.
2. I have an Excel file with all the EAN codes that I need an image of.

I would like to link each image with each cell in an extra column through a hyperlink.

Is this possible?

I have tried: =HYPERLINK("[Location pathway]\" & A1 & ".[filetype]","Click Here!")

I keep getting an error that it can't open the file.

Answer 1

Example below - I made a cell with the path and one with the file extension. Then I combined everything in another cell, like =path-cell & EAN-cell & file-type-cell. In D column I set the Hyperlink.

You can, of course, hide the rows and columns that you don't need.

Answer 2

Iv'e made some macros that might be of use:

My use case was 5000 links to images each image name was the image number ie. 2345.jpg

I needed to turn the number.jpg to a hyperlink to a folder in the same path as the sheet. The remove function is handy to reverse the process.

To use: change folder name, and if you need to reverse enter the regex of the filename.

Highlight the cells needed the run the macro, You may want to attach the macro to a button for ease.

Sub addHyperlinkFormula()

    Folder = "Images/"

    For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
    If cell <> "" Then
    Filename = cell.Value
    Formula = "=HYPERLINK(""" & Folder & Filename & """,""" & Filename & """)"
    cell.Formula = Formula
    End If
    Next cell
End Sub

Sub removeHyperlinkFormula()
    'this regex is for 4235435.jpg
    RegexPattern = "([0-9]+\.jpg)" 

    Set regex = CreateObject("VBScript.RegExp")
        regex.Pattern = RegexPattern
        regex.Global = True
    For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
        If cell <> "" Then
            Set allMatches = regex.Execute(cell.Formula)
            If allMatches.Count <> 0 Then
                result = allMatches.Item(0).submatches.Item(0)
                cell.Value = result
                cell.Font.Underline = xlUnderlineStyleNone
                cell.Font.Color = vbBlack
            End If
        End If
    Next cell
End Sub