VBA Excel Chart to powerpoint - saving file as multiple outputs

Question

I have a template in excel, and a powerpoint template.

in the powerpoint template I have several charts linked to the excel template.

I want to make a loop that changes something in excel, so that the chart updates, "saveas" for both the excel and powerpoint file, so I have eg. 100 different excel files and 100 different powerpoint files, all build on the templates. I want to be able to manually alter some data in those 100 excelfiles, with the link to powerpoint still intact, so they are updated too if needed.

however, when I open the powerpoint templates, right click on a chart and select "edit data", then the chart is still linked to the excel template, not the saveas version of the excel file?

how do I change the link of a chart in powerpoint, to a different excel filename? the structure in the two excel files are identical.

the chart is created in excel and copy/pasted as "keep source formatting and chain data" (translated from danish)

any help would be greatly appreciated.

I have also tried a solution where the copy/paste is done via vba into an empty powerpoint template, but that is more time consuming, and powerpoint often crashes.

Answer 1

Assuming you have a reference to the shape represented by the linked chart in oSh, this will show you the link:

Debug.Print oSh.LinkFormat.SourceFullName

And this will change it to some other file:

oSh.LinkFormat.SourceFullName = "c:\some\path\yourfile.xls"

Most likely there will also be a !chartname or the like after yourfile.xls ... you'll need to include that.

And save the Excel files first, THEN change the link in the PPT to point to them. If you change the link to point to a non-existent file, PPT won't throw any errors; it'll simply leave the link unchanged.