Insert Web Image 1

[psychonaut2000]

I'd like to insert a picture from a website into a spreadsheet and have that picture update automatically every time the image on the website updates (once a day). Specifically, I'd like to be able to insert a chart of a stock or index and have it update daily. Here's an example of what I want to include and autoupdate:

http://finance.yahoo.com/q/ta?s=^GSPC&t=1y&l=off&z=m&q=c&p=m50,m200,m100&a=vm,m26-12-9,ss&c=

Thx in advance for any help...

 

[ETID]

I've answered something likr this about a 1/2 year ago...

Let me research my archives.

 

[psychonaut2000]

Cool....thx!

 

[ETID]

1st...

Save the chart as a favorite in IE,...(I found the link by looking at the source html code from IE to return just the chart), the one you posted should work.

http://finance.yahoo.com/q/ta?s=^GSPC&t=1y&l=off&z=m&q=c&p=m50,m200,m100&a=vm,m26-12-9,ss&c=

then in your spreadsheet from the menu

insert>object> click the "create from file" tab.
then

then browse>favorites> ...select the http link (as stated above)

click insert...then OK

 

[psychonaut2000]

I followed those steps and I also clicked the "Link To File" but this created an object in Excel called "fc44bb33.tmp" rather than displaying the chart. When I double-click the object I get "You are about to activate an OLE object that may contain viruses, blah, blah, blah....you wanna continue?" and when I select "yes" I get the "file download" dialog and I have to choose "open" or "save". When I click "Open" I get an error indicating the file cannot be found. Any thoughts? Also, once we get this fixed, will the chart update in my spreadsheet when it updates on the website each day?

Thx for all your help!

 

[ETID]

Hmmm...are you using office XP?

 

[psychonaut2000]

Office 2003

 

[ETID]

I,ve seen this in office xp...had to turn off the link option...but i beleive there's a way way to force it through vba.

Does this chart link have a dynamic address....?


I.E. does it change with each update

 

[ETID]

try this code....

If it works for you you can put it in the on open event of the workbook



Sub get_yahoo_chart()

For i = 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Select
Selection.Delete
Next

Range("B2").Select 'this is where the chart will import to

ActiveSheet.OLEObjects.Add(Filename:= _
"

http://ichart.yahoo.com/z?s=^GSPC&t=1y&q=c&l=off&z=m&p=m50,m200,m100&",

_
Link:=False, DisplayAsIcon:=False).Select

Range("B2").Select 'returns focus to the sheet

End Sub

 

[ETID]

Here's another way...but the URL still concerns me, if I run the code two times in a row, I get a two different chart dates (located at top left of chart)



Sub get_yahoo_chart()

For i = 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Select
Selection.Delete
Next

Range("B2").Select 'this is where the chart will import to
ActiveSheet.Pictures.Insert( _
"

http://ichart.yahoo.com/z?s=^GSPC&t=1y&q=c&l=off&z=m&p=m50,m200,m100&").Select

Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)


Range("B2").Select 'returns focus to the sheet

End Sub

 

[ETID]

...If I paste the URL into Internet Explorer, I get a chart for July 14,...but IF I do a refresh the same URL, I get a chart for the 13th and multiple refreshes bring in either at random.


So the last code that I post is doing whats it's supposed to do, but I am at a loss for why I get random chart dates?

 

[psychonaut2000]

Sorry but what do I do with that code? Thx

 

[psychonaut2000]

Maybe try a chart from another site and see if that resolves the changing date issue. Try this:

http://www.marketscreen.com/chart/e...&type=4&vol=1&patterns=1&width=974&height=668

 

[ETID]

...ALt+F11 brings up the vba editor,

be sure that the editor is referencing the proper workbook
(there is a tree in the left frame)

You can paste the code into a new module (insert module from the menu) or into a "screen event" sub.

...the code goes into the right frame.

Let me know which approach you want to launch the macro with (menu driven or event driven).

 

[AliJay]

I have spent 2 full days looking for the solution to this problem and at last I found this post with eyes VERY blurred

Thank you ETID - please enjoy your star.