Inserting URL image

[gabethegrape]

In my Excel 2007 doc, I have the following code which works perfectly for inserting image from a local folder. I now have this other code for inserting images from a URL which only partially does what I need. If it's possible, I'd like the user to copy the URL for the image and when they press a command button, insert that image into the specified cell (similar to the first code). Here are the codes that I have so far:

For inserting from local folder:

Sub InsertPicture1()
Dim myPicture As String
Dim pic As Picture

myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", , "Select Picture to Import")
If myPicture = "False" Then Exit Sub

If myPicture <> "" Then
Set r = Range("b10")
Set pic = ActiveSheet.Pictures.Insert(myPicture)


    If pic.Height > pic.Width Then
        With pic
            .Top = r.Top
            .Left = r.Left
            .ShapeRange.LockAspectRatio = msoCTrue
            .Height = 206
            .Placement = xlMoveAndSize
        End With
    ElseIf pic.Width > pic.Height Then
        With pic
            .Top = r.Top
            .Left = r.Left
            .ShapeRange.LockAspectRatio = msoCTrue
            .Width = 208
            .Placement = xlMoveAndSize
        End With
     ElseIf pic.Height = pic.Width Then
        With pic
            .Top = r.Top
            .Left = r.Left
            .ShapeRange.LockAspectRatio = msoCTrue
            .Width = 208
            .Placement = xlMoveAndSize
        End With
    End If

End If
End Sub

and for the URL image:

Sub InsertPicture()
Dim myPicture As Shape
Set myPicture = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 2, 2, 200, 200)
myPicture.Fill.UserPicture "http://www.google.com/intl/en_ALL/images/logo.gif"
End Sub

 

[Andrew Poulsom]

You should be able to insert the picture just like you did from a folder:

Sub Test()
    ActiveSheet.Pictures.Insert "http://www.google.com/intl/en_ALL/images/logo.gif"
End Sub

 

[gabethegrape]

Hi Andrew,
Thanks for the reply. For some reason, in Excel 2007 that doesn't work (I get Error message "400"). That workaround seems to insert the url image fine, but I have to program that URL into VBA for the image to appear. I'm wondering if there is a way of inserting that URL directly from the sheet (protected sheet at that) by having the user copy the URL and with a command button have that image inserted into a cell.
Gabe

 

[gabethegrape]

The most obvious answers always escape a newbie...

Sub InsertPicture()
Dim myPicture As Shape
Set myPicture = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 2, 2, 200, 200)
myPicture.Fill.UserPicture Range("A1")
End Sub

 

[Andrew Poulsom]

Thanks for posting your solution.

In Excel 2007 my code produced error 1004. I also tried AddPicture, which produced a file not found error.

I could insert the picture manually, but the macro recorder didn't give any code. So I gave up, assuming it wasn't possible due to bugs.