Updating OLE linked Objects from Excel

[DeskMonkey]

Hi all,

Thanks for taking the time to read this:

I have a running PP Slideshow in our office which has 2 tables linked from 2 excel spreadsheets. All I want to do in the simplest possible way is update the charts in the presentation whenever one of the excel tables are updated.

For example, is there some code I could put in that would update all external links every time the source file is updated? Failing that, is there a PP way to pull the data from the spreadsheets without crashing with that "File is open by another user" message (as the Update Links Add in does - even when the workbooks are set to share)

This really is getting me down and I would be eternall yhelpful of any help.

I'm not really familiar with running code, so if someone has a VBA solution, please post the steps required to use it.

Again, thanks in advance of any help!

[stanl]

It will help to know which version of PP/Excel - there is a major difference between 2003 and 2007.

[DeskMonkey]

Thanks for the reply,

I am using PP 2007 and Excel 2007.

So far I have this macro:


Sub UpdateLink()
Dim osld As Slide
Dim oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then oshp.LinkFormat.Update
Next oshp
Next osld
End Sub


When I run the macro it updates the links as required, but what I need it to do is to run the update on every loop of the slideshow and either:


Ignore the source file if it in use and update next time around.
Simply read the source file while it is in use.I don't mind which of the two happen, so long as the updating does not stop the continuous loop.


Thanks again in anticipation of any help.


Also, although the code above does update the links, feel free to ament it/improve it if you can.