Insert Property of Picture Class

Question

I have read all of the questions on here about this topic and none of them provided me with a workable solution, so I'm asking this one.

I am running a legitimate copy of Excel 2013 in Windows 7. I record a macros where I insert a picture, and in the open file dialog I paste this URL: http://ecx.images-amazon.com/images/I/41u%2BilIi00L._SL160_.jpg (simply a picture of a product on Amazon). This works as expected.

The resulting macros looks like this:

Sub insertImage()
'
' percent Macro
'

'
    ActiveSheet.Pictures.Insert( _
        "http://ecx.images-amazon.com/images/I/41u+ilIi00L._SL160_.jpg").Select
End Sub

However, when I attempt to run this, the Insert line breaks with the following error:

Run-time error '1004':

Unable to get the Insert property of the Picture class

I am trying to insert a number of pictures into an excel document and I am using the ActiveSheet.Pictures.Insert method to do this. I have been experiencing this issue there, so I recreated it in a way others could replicate to facilitate getting an answer...

An interesting thing to note is:

http://ecx.images-amazon.com/images/I/41u%2BilIi00L._SL160_.jpg 'This is what I pasted
http://ecx.images-amazon.com/images/I/41u+ilIi00L._SL160_.jpg 'This is what the recorded macros recorded

It looks like Excel automatically resolved the %2B to a +. I tried making that change, but to no success.

Another interesting thing to note is that sometimes this does work and sometimes it doesn't. This url is a case where it does not work. Here's one where it does: http://ecx.images-amazon.com/images/I/51mXQ-IjigL._SL160_.jpg

Why would Excel generate a macros it can't run? More importantly, how can I avoid this error and get on with my work!? Thanks!

Answer 1

Try this workaround:

Sub RetrieveImage()
Dim wsht As Worksheet: Set wsht = ThisWorkbook.ActiveSheet
wsht.Shapes.AddPicture "http://ecx.images-amazon.com/images/I/41u+ilIi00L._SL160_.jpg", _
                    msoFalse, msoTrue, 0, 0, 100, 100
End Sub

All fields are required, which is kind of a bummer since you cannot get the default size. The location offsets and sizes are in pixels/points. Also, the % turning to + is just alright, as % would cause it to be not recognized (dunno why).

Result:

Let us know if this helps.

Answer 2

I'm experiencing the same issue. After some digging I found out Excel does a HTTP HEAD request before getting the image. If this HEAD request is unsuccessful Excel will return the exact error messages mentioned in this discussion.

Your could easily test this using Fiddler.