Add multiple pictures from local directory

[Leonvl]

I uded the below code to insert pictures based on a URL on the web based on a cell value which worked fine.
Now I would like to insert pictures for a range from a local folder, but that apparently doesnt work with the Parent.Pictures.Insert method. Played a bit with the Shapes.AddPicture method but cant get it working. Any thoughts here? Thanks

Sub InsImg()

Dim URL As Range
    
    For Each URL In Worksheets("Sheet1").Range("A4:ZZ4")
    With URL.Parent.Pictures.Insert(URL.Value)
        On Error Resume Next
        .Left = URL.Offset(0, 0).Left + 1
        .Top = URL.Offset(0, 0).Top
        .Height = 250
'        .Width = 420
        URL.EntireRow.RowHeight = .Height
    End With
    Next
End Sub

 

[Domenic]

Make sure that path and filename is valid for each one. Also, make sure that you have permission to access the folder. Also, try running the code again, but this time either comment out or delete...

On Error Resume Next

...before running it. Do you get an error? If so, what type of error do you get and on which line?

 

[Leonvl]

I am getting a 1004. "Property Insert of class Pictures cannot be retrieved"

The problem is here...

[COLOR=#333333]With URL.Parent.Pictures.Insert(URL.Value)[/COLOR]

The path and file name is valid and I do have permission to access the folder.
Taking out the "On error resume next" makes no difference.

 

[Domenic]

When the error occurs, place your cursor over URL.Value. What is its value?

 

[Leonvl]

The vlaue is the correct full path to the image: "C:\Images\ImageName.jpg"

 

[Domenic]

Just to be sure, try typing the following line in the Immediate Window and press ENTER...

? Dir("C:\Images\ImageName.jpg", vbNormal)

What does it return?

 

[Leonvl]

ImageName.jpg

 

[Domenic]

So it looks like you do in fact have a valid path and filename. Try using the AddPicture method of the Shapes object instead. Does this help?

 

[Leonvl]

Not sure how to compose the syntax

 

[Domenic]

Try...

Option Explicit

Sub InsImg()


    Dim oShape As Shape
    Dim rCell As Range
    Dim sFullName As String
    
    With Worksheets("Sheet1")
        For Each rCell In .Range("A4:ZZ4")
            sFullName = rCell.Value
            If Len(rCell) > 0 Then
                If Len(Dir(sFullName, vbNormal)) > 0 Then
                    Set oShape = .Shapes.AddPicture( _
                        Filename:=sFullName, _
                        LinkToFile:=msoFalse, _
                        SaveWithDocument:=msoTrue, _
                        Left:=rCell.Left, _
                        Top:=rCell.Top, _
                        Height:=-1, _
                        Width:=-1)
                    oShape.Height = 250
                    rCell.EntireRow.RowHeight = oShape.Height
                End If
            End If
        Next rCell
    End With
    
End Sub

Does this help?