Inserting URL image

gabethegrape

New Member
Joined
Mar 3, 2009
Messages
38
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:

Code:
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:

Code:
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

gabethegrape

New Member
Joined
Mar 3, 2009
Messages
38
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

New Member
Joined
Mar 3, 2009
Messages
38
The most obvious answers always escape a newbie...

Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top