asked on

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

ASKER CERTIFIED SOLUTION

John Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

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

John Wilson

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