Break links after inserting images using macro

[Samadhan Gaikwad]

HI,

I have a macro that adds images in "A" column from location according to manes listed in "D" column.
But I want to break their links bcoz when images are no longer present in location from where I inserted, no images are displayed.
I want them to delink and paste in "A" column only.

 

[Hui]

It could be either

or


Please let us know

 

[Chihiro]

You can just use ".Shapes.AddPicture" method instead of ".Pictures.Insert".

.AddPicture has "linktofile" as second argument, and 3rd argument is to save image with document.

Sub InsertImageShortName()
 
    Application.ScreenUpdating = False

Dim pic As String ' File path of a picture
Dim cl As Range

Set Rng = Range("A1:A2") ' Defining input range

For Each cl In Rng
 
    pic = "C:\Test\Sparky\" & cl.Offset(0, 3) ' Location of the picture file:
                                    ' "C:\Images" folder, with particular image name
                                    ' Located in the same row, third column from A, i.e. column D

    Set myPicture = ActiveSheet.Shapes.AddPicture(pic, False, True, cl.Left, cl.Top, -1, -1) ' Inserting picture from address in D column
                                                    ' into column A
    With myPicture
        .LockAspectRatio = msoTrue
        .Height = 150
    End With
    Next    ' Looping to the Nth row, defined in:
            ' " Set Rng = Range("A3:A10") "
 
    Set myPicture = Nothing
 
    Application.ScreenUpdating = True

End Sub

See link for details on Shapes.AddPicture.
https://msdn.microsoft.com/VBA/Excel-VBA/articles/shapes-addpicture-method-excel

 

[Samadhan Gaikwad]

Thank you, but this is not what I am looking.

It could be either
View attachment 43206
or


Please let us know

 

[Samadhan Gaikwad]

Yeah, working as expected, thank a lot!

You can just use ".Shapes.AddPicture" method instead of ".Pictures.Insert".

.AddPicture has "linktofile" as second argument, and 3rd argument is to save image with document.

Sub InsertImageShortName()

    Application.ScreenUpdating = False

Dim pic As String ' File path of a picture
Dim cl As Range

Set Rng = Range("A1:A2") ' Defining input range

For Each cl In Rng

    pic = "C:\Test\Sparky\" & cl.Offset(0, 3) ' Location of the picture file:
                                    ' "C:\Images" folder, with particular image name
                                    ' Located in the same row, third column from A, i.e. column D

    Set myPicture = ActiveSheet.Shapes.AddPicture(pic, False, True, cl.Left, cl.Top, -1, -1) ' Inserting picture from address in D column
                                                    ' into column A
    With myPicture
        .LockAspectRatio = msoTrue
        .Height = 150
    End With
    Next    ' Looping to the Nth row, defined in:
            ' " Set Rng = Range("A3:A10") "

    Set myPicture = Nothing

    Application.ScreenUpdating = True

End Sub

See link for details on Shapes.AddPicture.
https://msdn.microsoft.com/VBA/Excel-VBA/articles/shapes-addpicture-method-excel