Updating Links in Embedded Excel Objects

[Bill]

I'm new to the group, and I have searched for this answer, so please
forgive me if I've overlooked it.

I have a PowerPoint file that has 60+ embedded Excel objects. All of
the objects were created by embedding a new blank Excel object into
the presentation, then formulas were entered in each object that link
to a external Excel files. The external files are updated via a
separate process with monthly financial data. I would like to
preserve a copy of both the Excel and PowerPoint files at the end of
each month.

I thought that I would be able to save the Excel files under a month-
specific name, then update links in PowerPoint to point to the new
file. However, when I use the Edit | Links command within PowerPoint,
the external Excel files are not on the list.

Next, I tried copying all the files without changing their names into
month-specific directory. However, when I open the copied PowerPoint
file and check the links in the embedded Excel objects, they still
point to the files in the ORIGINAL directory.

The only way I have found to update the links manually is to open the
Excel object on each slide, then use the Edit | Links command from
within the Excel interface.

It appears there is some great advice here, and I am hoping that
someone has sample VBA code that will loop through my slides and
update the links automagically. Thanks in advance.

 

[Steve Rindsberg]

I'm new to the group, and I have searched for this answer, so please
forgive me if I've overlooked it.

I have a PowerPoint file that has 60+ embedded Excel objects. All of
the objects were created by embedding a new blank Excel object into
the presentation, then formulas were entered in each object that link
to a external Excel files. The external files are updated via a
separate process with monthly financial data. I would like to
preserve a copy of both the Excel and PowerPoint files at the end of
each month.

I thought that I would be able to save the Excel files under a month-
specific name, then update links in PowerPoint to point to the new
file. However, when I use the Edit | Links command within PowerPoint,
the external Excel files are not on the list.

No, because there *are* no links from PowerPoint to the external Excel files.
Just from the embedded Excel file to the external files, and PPT knows nothing
about what's in the embedded Excel file; it just activates it when
doubleclicked.

OLE links will always include the full path to he file you linked from, so even
though you copy everything to another folder, the embedded Excel file, when
activated, will look to the original files for updates.

BUT:

If it can't find the original files where they're supposed to be it'll look in
the folder with the PPT (or I think, Excel) file itself.

So what you want to try is copying all the files to your "monthly" folder then
(after making a backup) delete the originals. For the first round of tests,
don't just move or rename them ... Windows has a link-resolution service that
may try to outsmart you.

 

[Bill]

Steve, thanks so much for the reply. I did as you suggested (I
think): I copied the Excel and PowerPoint files to the month-specific
directory without renaming them, then I zipped up the originals, and
deleted the Excel & PP files from the original directory. I also
emptied the recycle bin just to be sure. When I open the month-
specific PP, all of the embedded Excel objects still have formulas
that reference the old path. It is not defaulting to the current
directory. Is there an option somewhere I need to set? When I go to
Edit | Links within each Excel object, it tells me the source file
cannot be found and allows me to redirect to the new path, but I still
have to do that with each object on every slide. Any other ideas?

 

[Steve Rindsberg]

Steve, thanks so much for the reply. I did as you suggested (I
think): I copied the Excel and PowerPoint files to the month-specific
directory without renaming them, then I zipped up the originals, and
deleted the Excel & PP files from the original directory. I also
emptied the recycle bin just to be sure. When I open the month-
specific PP, all of the embedded Excel objects still have formulas
that reference the old path. It is not defaulting to the current
directory. Is there an option somewhere I need to set? When I go to
Edit | Links within each Excel object, it tells me the source file
cannot be found and allows me to redirect to the new path, but I still
have to do that with each object on every slide. Any other ideas?

Well rats.

I think I see what's going on here. Hang on. It's a rollercoaster ride.

When you open a file in PPT in most (but not all) normal ways, it sets the
current directory to the directory where the PPT file's located.

When PPT can't find a linked Excel file, it looks in the current directory
(usually, but not always, the same directory as the PPT as we've just seen.)

In your case, there's another level of smoke, mirrors and misdirection. You're
opening an embedded Excel file from w/in PPT. A bit of prodding with the
VBAStick suggests that when you do this, Excel sets the current directory to My
Documents.

I embedded an excel file in a PPT, included this macro in the XLS:

Sub WhereAmI()
Msgbox CurDir
End Sub

I saved both files to a completely different drive than My Documents is located
on, but when I launch the PPT and doubleclick the linked Excel content then run
the macro, it points to My Documents.

Just to test this more thoroughly, and since you've got the files all set up
and ready to go, put a copy of the bits in My Documents and give it a whirl.

 

[Bill]

Steve, thanks again. I have not tried your My Documents suggestion.
I smashed some VBA routines together that I found and came up with
this PP macro that loops through all slides and changes the path. I'm
not very good at VBA, so I am sure that this could probably be more
efficient, but here it is:

Sub UpdateAllSheets()

Dim oShape As Shape
Dim oSlide As Slide
Dim oSheet As Object

' Loop through each slide in the presentation.
For Each oSlide In ActivePresentation.Slides

' Loop through all the shapes on the current slide.
For Each oShape In oSlide.Shapes

' Check whether the shape is an Excel linked OLE object.
If oShape.Type = msoEmbeddedOLEObject Then

' Found a sheet; obtain object reference, and
' then update.
Set oSheet = oShape.OLEFormat.Object
oSheet.ChangeLink "C:\oldpath\Financials.xls", _
"C:\newpath\Financials 2007-01-31.xls", xlExcelLinks
End If
Next oShape
Next oSlide
End Sub

I'm pretty sure this is working for me, but appreciate your feedback.

Thanks!
Bill

 

[Steve Rindsberg]

Steve, thanks again. I have not tried your My Documents suggestion.
I smashed some VBA routines together that I found and came up with
this PP macro that loops through all slides and changes the path. I'm
not very good at VBA, so I am sure that this could probably be more
efficient, but here it is:

Looks quite good to me. The only thing you might do is a test for the
OLEProgID on the ole object before assuming it's Excel. It might be an MSGraph
object or practically anything else.