Insert images with VBA - (Not link to them)

Question

I have some VBA code that adds images to a spreadsheet, and its all fine but when i send it out externally obviously the links wont work anymore as the other user is not on my network

how can i amend this code to insert the actual image in to the spreadsheet instead of linking to it

I am aware the excel file may get large in this scenario but they are small 20KB thumbnails i am inserting

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPict As Picture
Dim PictureLoc As String

    On Error GoTo EH
    Application.EnableEvents = False
    If Target.Column = 1 Then

        'Pictures.Delete

        PictureLoc = "\\ca-sbs-01\t\Shared\ExcelImages\" & Target.Value2 & ".jpg"


        With Target.Offset(, 1)
            Set myPict = ActiveSheet.Pictures.Insert(PictureLoc)
            .RowHeight = myPict.Height
            myPict.Top = .Top
            myPict.Left = .Left
            myPict.Placement = xlMoveAndSize
        End With

    End If
EH:
    Application.EnableEvents = True
End Sub

Use `ActiveSheet.Shapes.AddPicture` instead of `Pictures.Insert`. — Rory, Mar 11 '19 at 14:46
@SiddharthRout I was too lazy to look up the rest of the arguments. :) — Rory, Mar 11 '19 at 14:51

score 1 · Answer 1 · answered Mar 11 '19 at 14:56

You can use the Shapes.Addpicture method like this:

    Dim myPict as Shape
    With Target.Offset(, 1)
        Set myPict = Me.Shapes.AddPicture(Filename:=PictureLoc, linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=.Left, Top:=.Top, Width:=-1, Height:=-1)
    .RowHeight = myPict.Height
    End With
    myPict.Placement = xlMoveAndSize

Insert images with VBA - (Not link to them)

1 Answers1