Excel and PowerPoint Links

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
JagwarmanAsked:
Who is Participating?
 
JSRWilsonConnect With a Mentor Commented:
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
 
JagwarmanAuthor Commented:
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
 
JSRWilsonCommented:
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
All Courses

From novice to tech pro — start learning today.