Limitation on .Pictures.Insert?

[Dave]

Hello. I fear I may be running into a limitation of VBA, but figured
I'd check with the gurus to see if I'm doing anything silly.

When I manually insert a photo (Insert | Picture | <filename>) in
Excel (Office 12), it allows me to use an URL for my filename (eg.
http://www.seattlesmiths.com/photos/codi1.jpg). However if I try to
use an URL in my macro (ie ActiveSheet.Pictures.Insert("http://
www.seattlesmiths.com/photos/codi1.jpg")) it gives me the following
error:

"Unable to get the insert property of the Pictures class"

If I use a local photo, the call works correctly. Any ideas on how to
programatically insert a photo using an URL rather than a local file?

Thanks!
Dave

 

[Mike Fogleman]

I don't have 2007, but have you tried to record a macro while you do the
insert manually? This may get you the exact syntax required for an external
link.

Mike F

 

[Joel]

I think the solution is very simple. Remove "http:" from the file name.
http is the name of a transfer protocol (hyper-text-transfer-protocol).
Excel want a file name which simply starts with the two slashes.

 

[Dave]

I don't have 2007, but have you tried to record a macro while you do the
insert manually? This may get you the exact syntax required for an external
link.










- Show quoted text -

thanks for your response.

Strangely, when I use the macro recorder it just creates an empty
macro. this occurs both for inserting a local picture, and when I use
the URL.

 

[Dave]

I think the solution is very simple.  Remove "http:" from the file name. 
http is the name of a transfer protocol (hyper-text-transfer-protocol).  
Excel want a file name which simply starts with the two slashes.









- Show quoted text -

Hmmm. I fine idea, but I'm afraid it doesn't work. When running the
macro without the http: it pauses for a minute or so, then errors out
with "Insert method of Pictures class failed".

 

[Mike Fogleman]

I have heard 2007 won't record macros for drawing objects, it must apply to
pictures also. Anyway, my version does record and the code is exactly what
you tried to use and got the error. Of course 2000 & 2003 gives me a picture
of a black dog lying in the yard.
I would assume this is some security issue that 2007 has addressed
concerning web-based pictures being freely written to Excel. It may be as
simple as allowing some security option in Excel.
Good Luck,
Mike F

I don't have 2007, but have you tried to record a macro while you do the
insert manually? This may get you the exact syntax required for an
external
link.










- Show quoted text -

thanks for your response.

Strangely, when I use the macro recorder it just creates an empty
macro. this occurs both for inserting a local picture, and when I use
the URL.

 

[Lazzaroni]

Mike:

That is correct. When you try to record a macro for inserting an image, the
macro is blank.

I haven't found a way to insert an image from URL and only link to it. I
have been using AddShape and UserPicture, but there is no option to link only.

Ironically, it is possible to insert an image from a URL into a spreadsheet,
and even do it as either a link only, embedded only, or linked and embedded.
So it must be possible. If only we could reveal the method used by Excel 2007.

Any suggestions would be hugely appreciated.

Thanks.