Solved

Excel and PowerPoint Links

Posted on 2015-01-13
3
141 Views
Last Modified: 2015-01-16
I need to create a workbook in Excel with graphs and then copy to PowerPoint with the links in tact. This enables any changes that occur on the excel workbook to be mirrored in PP. [i.e. Each graph with show 13 months data,  Jan 14 - Jan 15]

Workbook and PP are saved as say Jan Data 15.xlsx and Jan Data 15.pptx

I need to create new workbooks each month and would like to use the original as a kind of template, meaning each and every graph will be used each month but with new data to reflect that months data. [So in Mar we will add data for Feb etc]

ok here is the problem, if I save the original Excel  and PP with new names Feb Data 15.xlsx and Feb Data 15.pptx the links to the Jan files get broken.

Is there a way to make the links remain with the original files and when creating the new months files to have their own links.

I hope this is self explanatory
0
Comment
Question by:Jagwarman
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
JSRWilson earned 500 total points
ID: 40546413
No automatic way unless you REPLACE the Excel file with the new data (saving a copy of the original elsewhere.)

A better way would be in PPT
FILE > Info
In the Right Pane > Edit Links to Files

Select "Change Source"
0
 

Author Comment

by:Jagwarman
ID: 40546515
so you are saying that I would need to copy all the data onto PowerPoint each month, and there will maybe 20 tabs with 4 graphs on each tab.  

I tried what you said

A better way would be in PPT
 FILE > Info
 In the Right Pane > Edit Links to Files

 Select "Change Source"

and this works fine for one tab but when I add multiple tabs.......i.e. 20, not good
0
 
LVL 23

Expert Comment

by:JSRWilson
ID: 40546537
You could change the links with code all at once. Would that be a solution?

Code might look like

Sub update_Links()
Dim linkadd As String
Dim osld As Slide
Dim oshp As Shape
On Error Resume Next
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
Err.Clear
linkadd = oshp.LinkFormat.SourceFullName
If Err = 0 Then
' you will probably need to adapt the search
oshp.LinkFormat.SourceFullName = Replace(Expression:=linkadd, Find:="Jan Data 15", Replace:="Feb Data 15")
oshp.LinkFormat.Update
End If
Next oshp
Next osld
End Sub

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In all recent versions of PowerPoint it is possible to trigger animations. This means the animation takes place when a certain shape is clicked. This allows you to run animation “on demand” and outwith the normal sequence of mouse cl…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question