How do I convert URL (in a worksheet) to an image-CodePudding

I have this code to convert a set of URLs in column B to images in column C, but i get the error :

Unable to get the Insert property of the Pictures class. My code :

Private Sub Insert_Pic()

Dim pic As String
Dim myPicture As Picture
Dim rng As Range
Dim item As Range

lRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

Set rng = Range("B3:B" & lRow)
    For Each item In rng
        pic = item.Offset(0, -1)
        If pic = "" Then Exit Sub
            Set myPicture = ActiveSheet.Pictures.Insert(pic)
            With myPicture
                .ShapeRange.LockAspectRatio = msoFalse
                .Width = item.Width
                .Height = item.Height
                .Top = Rows(item.Row).Top
                .Left = Columns(item.Column).Left
                .Placement = xlMoveAndSize
            End With
    Next

End Sub

Thanks for your help

CodePudding user response：

Ok... so the algorithm in debugging is to start with something tiny, that works and then to continue.

For a beginning - take this 4 lines only and run them:

Sub TestMe()

    Dim myPicAddress As String
    myPicAddress = "https://www.vitoshacademy.com/wp-content/uploads/2016/02/va2.png"
    Dim myPic As Picture
    Set myPic = ActiveSheet.Pictures.Insert(myPicAddress)
    
End Sub

Then, start working on your code, putting the With-End With part to the code, that already works:

Sub TestMe2()

    Dim myPicAddress As String
    myPicAddress = "https://www.vitoshacademy.com/wp-content/uploads/2016/02/va2.png"
    Dim myPicture As Picture
    Set myPicture = ActiveSheet.Pictures.Insert(myPicAddress)
    
    Dim item As Range
    Set item = ActiveSheet.Cells(5, 5)
    
    With myPicture
        .ShapeRange.LockAspectRatio = msoFalse
        .Width = item.Width
        .Height = item.Height
        .Top = Rows(item.Row).Top
        .Left = Columns(item.Column).Left
        .Placement = xlMoveAndSize
    End With

End Sub

At the end, take a look at the loop and what is passed by as a picture string. Probably the error is hidden somewhere there. Have fun :]