Using VBA to embed existing linked images into Excel (2010)

[Dunclair]

I have an exising report generated in excel which has a thumbnail of a linked .jpg file inside the 1st cell of every row. There are 100's of rows each with a unique thumbnail and assosiated .jpg file. This excel file needs to be distributed to several other people. When distributed all the image links break and the thumbnails can no longer be viewed.

If possible i'd like to write a macro which looks at an existing image link and replaces the thumbnail with an embeded image... although if anyone has an alternative method of solving this problem i'll take any advice!

Many thanks

 

[Worf]

Welcome to the Board

When hovering a thumbnail with the mouse, does it change to a hand and a path is displayed?

 

[Dunclair]

Hi!, thanks for responding Yes, the full path is displayed with a hand icon

 

[Worf]

See if this helps:

Sub hlink2()
Dim sh As Shape, i%, ws As Worksheet
Set ws = ActiveSheet
For i = 1 To ws.Shapes.Count
    InsertPicture ws.Shapes(i).Hyperlink.Address, ws.Shapes(i).TopLeftCell.Offset(, 4), 1, 1
Next
End Sub

Sub InsertPicture(fn$, TargetCell As Range, CenterH As Boolean, CenterV As Boolean)
Dim p As Object, t&, L&, w&, h&
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(fn) = "" Then Exit Sub
Set p = ActiveSheet.Pictures.insert(fn)
With TargetCell
    t = .Top
    L = .Left
    If CenterH Then
        w = .Offset(, 1).Left - .Left
        L = L + w / 2 - p.Width / 2
        If L < 1 Then L = 1
    End If
    If CenterV Then
        h = .Offset(1).Top - .Top
        t = t + h / 2 - p.Height / 2
        If t < 1 Then t = 1
    End If
End With
p.Top = t
p.Left = L
Set p = Nothing
End Sub