How to Unlink an Image/Picture in a spreadsheet to its original path

[Abegail0203]

Hi,

I have an existing macro that convert the path in a cell into a picture. The problem with my macro is that everytime i delete the file in jpg format in the folder/path location after running the macro, the picture inserted in the spreadsheet is no longer displayed. The error says "The linkage image cannot be displayed. The file may have been moved, renamed or deleted.

I am asking your assistance what code should be inserted in order for the images to no longer dependent on the original path because the original location should remain empty after embedding the picture/image inside the spreadsheet.

Here's my existing code:



Dim P As Object
For Each Sheet In Sheets
Sheet.Activate
On Error Resume Next
Range("A12").Select
Set P = ActiveSheet.Pictures.Insert( _
Range("A12").Value _
).Select

Cell A12 is where the path is written. Some of the sheets have path in range A12, some don't have that is why i use the "FOR EACH SHEET IN SHEETS".


Thank you in advance
Aby

 

[Umma123]

You should try this line of code .placement = xlMoveAndSize

 

[Abegail0203]

You should try this line of code .placement = xlMoveAndSize

Where should i put this in my existing code? Apologies as I am only a beginner.

 

[Umma123]

You should insert the line .Placement = xlMoveAndSize right after the picture is inserted in your macro. This will ensure that the image is embedded and moves/resizes with the cells.

 

[jolivanes]

Use "Shapes.AddPicture"

Set myPicture = ActiveSheet.Shapes.AddPicture( _
Filename:=item.Value, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)

If you want an example, let us know.

 

[jolivanes]

ActiveSheet.Shapes.AddPicture "C:\Picture Folder Name\Picture Name.jpg", False, True, Columns(5).Left, Rows(2).Top, -1, -1

The twice use of "-1" is to keep the picture original height and width. Change as desired.
Picture left will be at Column E left and top at Row 2 top.

Sub Tested()
Dim shp As Shape
Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Len(Sheets(i).Cells(12, 1)) <> 0 Then Set shp = Sheets(i).Shapes.AddPicture(Sheets(i).Cells(12, 1).Value, False, True, Sheets(i).Columns(5).Left, Sheets(i).Rows(2).Top, -1, -1)
    Next i
End Sub

If you use "Pictures.Insert" it references a file.
"Shapes.AddPicture" permanently inbeds picture.

 

[jolivanes]

I read your Post #1 again and I am confused. Nothing out of the ordinary you might say.
At the end you mention that Cell A12 is where the path is written.
In order to insert a picture you need the full path, picture name and extension. (You can get by without extension but that seems to be of no concern here.)
Does Cell A12 have the path with picture name and extension?
If all pictures will be imported from the same folder, you don't need the path in that cell as you can hard code it in the code. Obviously, we need to know what it is.
I have added to the previous code in case Cell A12 is empty and a check that a picture with that path & name exist.

Sub Tested()
Dim shp As Shape
Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Len(Sheets(i).Cells(12, 1)) <> 0 Then
            If Not Dir(Sheets(i).Cells(12, 1)) = vbNullString Then Set shp = Sheets(i).Shapes.AddPicture(Sheets(i).Cells(12, 1).Value, False, True, Sheets(i).Columns(5).Left, Sheets(i).Rows(2).Top, -1, -1)
        End If
    Next i
End Sub

 

[Abegail0203]

I read your Post #1 again and I am confused. Nothing out of the ordinary you might say.
At the end you mention that Cell A12 is where the path is written.
In order to insert a picture you need the full path, picture name and extension. (You can get by without extension but that seems to be of no concern here.)
Does Cell A12 have the path with picture name and extension?
If all pictures will be imported from the same folder, you don't need the path in that cell as you can hard code it in the code. Obviously, we need to know what it is.
I have added to the previous code in case Cell A12 is empty and a check that a picture with that path & name exist.

Sub Tested()
Dim shp As Shape
Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Len(Sheets(i).Cells(12, 1)) <> 0 Then
            If Not Dir(Sheets(i).Cells(12, 1)) = vbNullString Then Set shp = Sheets(i).Shapes.AddPicture(Sheets(i).Cells(12, 1).Value, False, True, Sheets(i).Columns(5).Left, Sheets(i).Rows(2).Top, -1, -1)
        End If
    Next i
End Sub

Thanks @jolivanes.