5

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!

pnuts
  • 58,317
  • 11
  • 87
  • 139
kentcdodds
  • 27,113
  • 32
  • 108
  • 187

2 Answers2

3

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:

enter image description here

Let us know if this helps.

WGS
  • 13,969
  • 4
  • 48
  • 51
  • 1
    Curious... I copy and pasted your code exactly and it gave me a run-time error: "The specified file wasn't found." on the `AddPicture` line. – kentcdodds Dec 06 '13 at 05:40
  • Are you using `http://ecx.images-amazon.com/images/I/51mXQ-IjigL._SL160_.jpg`? Don't use that one, use the original one recorded from your macro. – WGS Dec 06 '13 at 05:55
  • Curious and curious-er, dear Watson. What if you use the URL working for you? Because on my end, your working URL is throwing the `specified file...` error. – WGS Dec 06 '13 at 06:06
  • 1
    Someone at Microsoft is just trolling us or something because when I use: `http://ecx.images-amazon.com/images/I/51mXQ-IjigL._SL160_.jpg` it works fine, but when I use `http://ecx.images-amazon.com/images/I/41u+ilIi00L._SL160_.jpg` it breaks... – kentcdodds Dec 06 '13 at 21:41
  • 1
    Won't be surprised with the troll. Excel 2013 and Windows 8 are perfect examples of trolling anyway. ;) Hope this helped, even if it's not a perfect answer. :) – WGS Dec 07 '13 at 01:22
3

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.

RoelVB
  • 1,586
  • 11
  • 12