Macro for linking cell to an image

[Dunca1]

Hi Guys,

I'm new to this forum so apologies if this questions has been answered or it's a trivial question. I have searched the forum and google and have been unable to find a decent solution but this could be because I'm not searching using the correct terms etc.

I have a load of images I need to link to images in an excel file and so far I've been hyperlinking them manually which is taking ages. Is there a way to run a macro to automatically link the cell to a specific image based on some information in the row that the cell is situated on?

Extra question that doesn't need to be answered

I thought I might as well ask this question whilst I'm here but obviously feel free to skip this as it's just a general question. I'm a recent mechanical engineering graduate and never had a need to use VBA but I absolutely love excel (as I'm sure all you do and all engineers do) and would like to learn more for personal interest and for streamlining a lot of processes in my graduate role. Where should I start with this? Books, video courses etc. would be an ideal start where examples are used that I can follow for my own projects. But, if you have other suggestions I'm open.

Thank you everyone, Duncan.

 

[Mentor82]

Hi,
Welcome on board of MrExcel!?
Let me start with your first question. It’s possible to have a macro that would create a hyperlink to he image file located in some directory. I assume you store files in a folder on a drive, right? To create a hyperlink it required to know a the folder/folder directory where the file is stored and file name including file extension (if you use one file extension it can be hardcoded). Do you have one foder where you keep images or more?

regards,
Sebastian

 

[Dunca1]

Hi,
Welcome on board of MrExcel!?
Let me start with your first question. It’s possible to have a macro that would create a hyperlink to he image file located in some directory. I assume you store files in a folder on a drive, right? To create a hyperlink it required to know a the folder/folder directory where the file is stored and file name including file extension (if you use one file extension it can be hardcoded). Do you have one foder where you keep images or more?

regards,
Sebastian

Hi Sebastian,

Thank you!

Yes the files are kept in a folder on a drive. They are all currently in 1 folder but they could possibly be put into sub-folder in the near future. However, if the macro saves me a lot of time then I don't have to put the images into sub folders.

Thanks,
Duncan

 

[Dunca1]

Bump (if this is not allowed please let me know)

 

[yky]

Bump (if this is not allowed please let me know)

I use the following routine to add hyperlink to photos. The name of photos are in B3:B210.

Sub add_photo_link()
' add photo link to cells

    Application.ScreenUpdating = False
    Dim cell As Range
    Dim rng1 As Range
    Dim p_path as string

    Set rng1 = Range("B3:B210") 'this is the range of cells that contain the name of the photo (without extension)
    p_path = "C:\test\"

    For Each cell In rng1
        'if name of photo is not empty, photo does exist in the designated directory, and the destination cell has no hyperlink
        If cell.Text <> "" And Len(Dir(p_path & cell.Text & ".jpg", 0)) <> 0 And cell.Hyperlinks.Count = 0 Then
  
            ActiveSheet.Hyperlinks.Add Anchor:=cell.offset(0, 83), address:=p_path & cell.Text & ".jpg"

        End If
     Next cell

    Application.ScreenUpdating = True

End Sub