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

user_16296257 0
Viewed 60

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

url vba excel
1 Answers

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.

user_54486260
Related