How to update linked Excel charts in PowerPoint 2007?

[jim]

I have a PowerPoint 2007 deck (.pptx format) with about 150 charts in it that
were copy-pasted out of a few different Excel 2007 workbooks. The workbooks
have links to other files, and the data and charts in the workbooks are
updated automatically.

Question 1:
My problem is around updating the charts in the PowerPoint file. When I open
the deck and go to the Office button-->Office button-->Prepare-->Edit Links
to Files command, I get about 150 links to update: one per chart. There are
only 4 or 5 linked Excel files: is there any way to update all links to the
same file together, rather than doing it one at a time?


Question 2:
When we pasted the Excel charts into the PowerPoint deck, they went in with
absolute, rather than relative links. This is an issue because some of my
co-workers are remote, and will download work to their local drives, update
the file, then return it to the network. As a result, all the links in the
PowerPoint are now absolute links referencing files on my co-worker's C
drive.

This is not a showstopper because we can have him update the files on his C
drive, but it is inconvenient. Is there a way to force relative links in
PowerPoint? That way, if we build the deck in the same directory as the
workbook with the data, we'll be able to move it around freely as long as we
copy/paste the whole directory as a single unit.

Thank you for your help!

 

[Steve Rindsberg]

Question 1:

My problem is around updating the charts in the PowerPoint file. When I open
the deck and go to the Office button-->Office button-->Prepare-->Edit Links
to Files command, I get about 150 links to update: one per chart. There are
only 4 or 5 linked Excel files: is there any way to update all links to the
same file together, rather than doing it one at a time?

Have a look here:

Search and Replace for Hyperlinks
http://www.pptfaq.com/FAQ00773.htm

Question 2:
When we pasted the Excel charts into the PowerPoint deck, they went in with
absolute, rather than relative links. This is an issue because some of my
co-workers are remote, and will download work to their local drives, update
the file, then return it to the network. As a result, all the links in the
PowerPoint are now absolute links referencing files on my co-worker's C
drive.

As long as the PPT and linked files are in the same folder, no matter where that
folder might be, you should be ok, witness the fact that your co-workers were
apparently able to do just that.

OLE links cannot be relative; they're always absolute, but if the file's not
where the link points, PPT should look in the current folder for the file.

 

[jim]

Hi Steve,

Thank you very much for your help. That macro does seem to be what I'm
looking for - I'll give it a shot and post here with my results.

As far as the deck defaulting to looking in its current directory if it
can't find linked files (which would be the easiest solution), that's not
what I've been observing. When I paste the deck into a directory on our file
server (this directory also contains the linked data files with the correct
file names), linked data does not update. The deck just looks for files on my
coworker's C drive. Is there a setting or command that I need in order to
have the deck search in its current directory for linked files?

Thanks again,
Jim

 

[jim]

Hmm, I don't know if I'm doing something wrong or what, but the macro doesn't
do anything for me. There are no error messages, but when I put in my "find"
and "replace" text and run it, the macro dialog box disappears and nothing
happens - no "string not found", no "macro crashed", and the links aren't
updated. Do you have any suggestions?

Thanks,
Jim

 

[Steve Rindsberg]

As far as the deck defaulting to looking in its current directory if it

can't find linked files (which would be the easiest solution), that's not
what I've been observing. When I paste the deck into a directory on our file
server (this directory also contains the linked data files with the correct
file names), linked data does not update. The deck just looks for files on my
coworker's C drive. Is there a setting or command that I need in order to
have the deck search in its current directory for linked files?

Oh jeez, has somebody improved THIS too? :-(

I don't know of any setting that would affect this; anyone?

 

[Steve Rindsberg]

Hmm, I don't know if I'm doing something wrong or what, but the macro doesn't
do anything for me. There are no error messages, but when I put in my "find"
and "replace" text and run it, the macro dialog box disappears and nothing
happens - no "string not found", no "macro crashed", and the links aren't
updated. Do you have any suggestions?

Try tracing through the code ... put a breakpoint on this line:

oSh.LinkFormat.SourceFullName = _
Replace(oShape.LinkFormat.SourceFullName, _
sSearchFor, sReplaceWith)

and see if it gets triggered. Also, in order for it to work, you need to have a
copy of the linked file on the indicated path; ie, you can't set a link to a
file that isn't there.

Ah. And you're in Office 2007. My mom wouldn't let me have one of those ant
farm things when I was a kid and now she won't let me bring Office 2007 in the
house now. Links are buggy as all getout. The hyperlinks code won't work at
all in 2007 and there may be issues with the OLE relinking as well.

Try tracing and let me know what you find out.

 

[jim]

Hi Steve,

Thanks very much for your followup with this. It sounds like PowerPoint 2007
is the culprit in the links not working. I'll try the added code that you
suggested, but I also wanted to let folks know that I found a workaround: a
piece of software called PowerPointPipe. It lets you search and replace all
kinds of data in PowerPoint, including OLE and hyperlinks, and it works in
PPT 2007. Like the macro that you proposed, this software also has the
limitation that you can't force a link point to a file that's not there.

Actually, this is more of a feature than a drawback in my mind - it prevents
possible mistakes. Anyway, using PowerPointPipe allowed me to change the
links to point to files on our fileserver, and all charts then updated
correctly. (Note that I needed to force links to update manually using the
command on the "prepare-->edit links to files" dialog box - telling the file
to update on opening didn't actually update everything.)

Thanks again!

-Jim

 

[Steve Rindsberg]

Hi Steve,

Thanks very much for your followup with this. It sounds like PowerPoint 2007
is the culprit in the links not working. I'll try the added code that you
suggested, but I also wanted to let folks know that I found a workaround: a
piece of software called PowerPointPipe. It lets you search and replace all
kinds of data in PowerPoint, including OLE and hyperlinks, and it works in
PPT 2007. Like the macro that you proposed, this software also has the
limitation that you can't force a link point to a file that's not there.

Just FWIW, it's not a limitation in the software, it's a limitation in PowerPoint
and/or OLE.

IAC, thanks for the pointer.

 

[João (Portugal)]

Just FWIW, it's not a limitation in the software, it's a limitation in PowerPoint
and/or OLE.

IAC, thanks for the pointer.

Any other PPT MVP can answer this? (Steve wants us to buy Purchase FixLinks
($69.95) )

Hi, (posted in microsoft.public.office.misc )

I've been searching the net for this issue and came to some payed
replacers (Steve), but no answers from microsoft (except MVP Steve).
In Powerpoint 2003, if we inserted a linked Excel sheet that link was
a relative one.
We do the same in PPT 2007, with Excel 2007 and it's a absolute link.
Now I've read the trick to export/publish the PPT to a CD in order to
convert the links from absolute to relative... I've tried it and it's
unpracticable with 45 links (to 45 Pivot tables).


Seams also cumbertone that in Excel if you referer several cell from
an external spreadsheet it's only one link you need to change; but in
powerpoint those several links to cells from one sheet will be come
several links.


And it's curious that I have another presentation where I mantain like
40 absolute links and 5 relative links to an Excel sheet.


The next nightmare is when I open the presentation (with the 45 links)
that even when I say not to update links, It will try to open 45 times
the Excel sheet. It's so cumberstone that I need to open the Excel
sheet before I open the presentation, even if I say not to update.


Imagine the cenario when you create tens of presentations like this
for people, in which the sole diference between the PPTs is some
parameters in the SP/query that feeds the pivot tables:
- make a copy of the .xlxs and .pptx to a new folder
- open the older .xlsx (in the original folder) so speed up the
new .pptx opening
- open the new .pptx, and say not to update links - anyway it will
try to open the old .xlsx 45 times, but it's ok because it's open
- go to prepare and edit links to files, and change origin selectting
45 times a .xlsx from the current folder.
- now close the older .xlsx and open the .xlsx it the new folder.
- go to data and change the SP/queries parameters, and save .xlsx
- save the pptx, close it and reopen it (the absoute links are now
pointing to the new xlsx) and choose update links (we've updated the
45 pivot tables after changing the links).


- and finally repeat this tens of times.


I'm sorry for my bad english, and for the size of the post, but I
haven't seen anyone before complaning about this issue in Office 2007.
Thank you in advance for a MVP that can look into my ''little"
problem,


João Ferreira (Lisboa - Portugal)

 

[Spazzeroony]

People, I have had the exact same problem. No offense to the Micosoft Office
Experts, but it seems you guys are not catching what the users are asking.
I'm unsure if it is that the users do not know how to explain themselves
properly or maybe because you guys do not understand the situation or
frustration of the users.

I know what it feels like because I have experienced it first-hand this week
(13 July 2009 to 16 July 2009).

Between myself and my collegue, we have done extensive research on this
issue and have found a workable and quite friendly solution to the problem.

The problem is this; When you have a "Display" machine like a "Kiosk" in the
staff canteen. You would like to have a PowerPoint presentation running there
24/7. The problem is to update this presentation without touching the
"Dispaly" PC. PowerPoint does not allow a smooth update to the presentation
without user intervention. The Presentation should work FOR YOU, and YOU FOR
the presentation.

Guys, if you would like to know the solution, please, e-mail me on
(e-mail address removed), I will gladly help you to solve this problem!

 

[Spazzeroony]

Steve, sorry for posting so many times. I just wanted to have as many people
as possible know I found a nice solution to this problem. ;-)