CODEWITHSUNDEEP
excelvbapowerpoint
Clemens
Clemens

Reputation: 11

Links to Excel break in Powerpoint

I am using Office 2016. I created dynamic Charts in Excel, meaning that I named data ranges which expand as new data is fed into the excel. I transfered the Excel Charts to PowerPoint and pasted with "Keep Source Formatting & Link Data". However, as soon as I close and reopen the PowerPoint and refresh the graphs, the link to the Excel gets lost. This is how the data source looks like:

As you can see, instead of the Excel file Name I only get "[0]"

I tried to find the source for this mistake but couldn't find anything other than this Forum entry without solution: Problems with Linked Excel Charts in Powerpoint

I have tried to paste the graphs as excel chart object in PowerPoint and in this case to problems with the link do not occure. However, I hope to find another solution since I need to format the graphs in PowerPoint, which is not possible with Excel chart objects.

Since I couldn't find any documentation on this bug, I thought could maybe work around by using a VBA to replace the [0] in the chart data source with the actual file path. If I do so manually it solves the problem - until I save and close the ppt again. I tried several scripts I found here to change links to excel graphs in powerpoint, but wasn't able to make any of them work.

My question: 1) Is anyone familiar with this problem with dynamic excel-graphs in powerpoint and can point me to a solution? 2) If not: could anyone point me to a VBA script which can replace [0] with a correct filepath?

Thank you very much for any help and best regards, Clemens

Upvotes: 0

Views: 2054

Answers (1)

Clemens
Clemens

Reputation: 11

I finally was able to get to the desired results by using Pivots. I am generally not a big fan of Pivots but I came to find out that there are no Problems with Pivot-Charts in PowerPoint. However, the formatting of Pivot Charts is not very stable in Excel. Simple Charts are fine, but Charts with different Chart types, secondary axis, data lables etc. tend to Show changes when updated, like for instance changing number formatting, positions of data lables and adding marks to line Charts. I found the Charts to be more stable in PowerPoint.

In summary I have originally tried to make automatically updating Graphs in PowerPoint with dynamic ranges in Excel, but the link from PowerPoint to Excel breaks once the presentation is closed and repoened. I tried to fix the broken links with a Makro in PowerPoint, but wasn't able to figure it out. I then tried to use date axis for the Charts instead of text axis. This way I was able to have a fix Chart range that is bigger than the data available, the Chart is still displayed correctly and new data Comes into the Chart automatically. However, this led my Excel to crash whenever I updated the data for all Graphs. Finally I was able to solve the Problem with Pivots with only minor Problems up until now.

Thanks jkpieterse for the help, the Pivot solution is close to the Get&Transform that you suggested.

Upvotes: 1

Related Questions