Change Source for Links to Excel charts from PowerPoint

  • I have been given the job to update a powerpoint which has about 100 charts in it that link to a single (very large) excel spreadsheet.


    At present these links refer to an old version of the excel file in a specific file locations. The only way I can get the update link function to work is to rename the latest version of the excel spreadsheet as its old name and put in the old file location before I open the powerpoint.


    As all the charts update from the same excel spreadsheet I would like to be able to update the file name and location for all the links at the same time. Ideally having a window come up that prompts the users to point to the excel file from which the powerpoint should update everytime. Is this possible with VBA?


    It would save me a huge amount of time if I could! You help is appreciated.

  • Re: Change Source for Links to Excel charts from PowerPoint


    I ran into a similar problem and this is the best I could come up with. I'm sure there is a better way to do this. You have to use the entire with block below for every chart. You can find the name of the shape in powerpoint by going to Home --> Select --> Selection Pane.


    Sub modifylinks()


    Dim ExcelFile
    Dim exl As Object
    Set exl = CreateObject("Excel.Application")


    ExcelFile = exl.Application.GetOpenFilename(, , "Select Excel File")



    'Update Slide 2 charts
    With ActivePresentation.Slides(2).Shapes("Insert Shape Name Here")
    If .Type = msoLinkedOLEObject Then
    With .LinkFormat
    .SourceFullName = ExcelFile & "!Charts![Insert File Name.xlsm]'Insert Tab Name with Charts in it' 'Insert Chart Name'"
    .AutoUpdate = ppUpdateOptionManual
    End With
    End If
    End With


    End Sub


    Example...


    With ActivePresentation.Slides(2).Shapes("Project Resources")
    If .Type = msoLinkedOLEObject Then
    With .LinkFormat
    .SourceFullName = ExcelFile & "!Charts![Project Information.xlsm]Charts Resources"
    .AutoUpdate = ppUpdateOptionManual
    End With
    End If
    End With

  • Re: Change Source for Links to Excel charts from PowerPoint


    I know it's too late for the OP, but I've written a solution that might useful to others.


  • Re: Change Source for Links to Excel charts from PowerPoint


    Hello wilsoff...I tried your code and while it updates the source file it does not capture the cell references leaving the code useless in the specified case. I have tweaked a couple parts of different codes and have gotten close, the problem I encounter is Excel charts have the file name and worksheet listed twice (i.e. workbook.xls!Graphs![workbook.xls]Graphs Chart 3) and the formula does not change the second occurrence of the workbook name in brackets. Below is the formula, if anyone can figure out a way to adjust the formula to also update the workbook in brackets to the new file I would greatly appreciate it!


  • Re: Change Source for Links to Excel charts from PowerPoint


    My code works for me.


    I've tried to investigate this, but come up blank.


    If I add a message box to show me .SourceFullName then it only shows me the first reference to the xlsx, with no square brackets or repeated xlsx filename


    If I add the same message box to your code it suggests the .SourceFullName is blank


    Can you it in yours and let us know what the msgbox shows please?


    Although, I'd suggest your VBA is better than mine looking at your much tidier code!

  • Re: Change Source for Links to Excel charts from PowerPoint


    Thanks for the quick reply wilsoff, this has been driving me nuts for at least a week. For your reference I am a complete novice when it comes to VBA code, I begged, borrowed, and stole the code that I referenced. I took screen shots of the edit links box after I ran the macro to show you what I am seeing. Hopefully this helps...


    [ATTACH=CONFIG]42783[/ATTACH]

  • Re: Change Source for Links to Excel charts from PowerPoint


    Hmmm... we must've used different methods to get the charts into powerpoint.


    I do it by going to excel, clicking on the chart, copying, then pasting it into the powerpoint slide. This pastes it as Type: Chart.


    Screenshot: [ATTACH=CONFIG]42786[/ATTACH]


    Yours is showing Type: Excel Worksheet


    Without being able to get it to show me the same thing it's tricky to develop somethign to fix it. If I were trying to do it I would use this logic:


    read the whole name into a wholeString
    find the position of the open square bracket in wholeString
    copy everything upto and including the square bracket into a new variable, startString
    find the position of the close square bracket in wholeString
    copy the close square bracket and everything after it into endString
    then concatenate the parts with: wholeString = startString & ExcelFile & endString


    You may have to play with ExcelFile to get it to just give you just the filename without the full path - possibly with yet another variable, say ExcelFileShort


    Good luck, and Merry Christmas.

  • Re: Change Source for Links to Excel charts from PowerPoint


    Thanks again for the quick response. I copy and paste special > paste link using Excel 2003 so that might be where the disconnect lies. I appreciate the advice, I have tried variations of that with limited success (in all honesty no success). Not all of the OLE Objects in my PowerPoint are Excel charts (some are direct references to cells) which means they don't have the brackets which seems to be causing a problem. I will keep plugging away though and will post an update if I find a solution. Have a Merry Christmas and happy New Year!

  • Re: Change Source for Links to Excel charts from PowerPoint


    Try this:

  • Re: Change Source for Links to Excel charts from PowerPoint


    BRILLIANT! That did the trick. I cannot thank you enough. I have searched high and low on Google the past week and a half and was not able to find a solution. Simply marvelous!

  • Re: Change Source for Links to Excel charts from PowerPoint


    I found an issue with my latest version...


    (1) It doesn't update the path properly - only the filename - not an issue if you kep everything in the same place
    (2) It can't handle paths / filenames with spaces in them


    I've tried for a couple of hours to fix it, but to no avail.

  • Re: Change Source for Links to Excel charts from PowerPoint


    Good to know, but luckily we will be keeping everything in the same location so shouldn't be a problem. I did a quick test yesterday through the entire process and it worked. The small limitation of having to keep the files in the same location is a small price to pay to be able to systematically change the source file which you accomplished. My thanks again!

  • Re: Change Source for Links to Excel charts from PowerPoint


    Hey guys,


    I found this page while trying to find a solution to a similar problem. Mine differs from joeyf1120 in the fact my powerpoint presentation links to a multitude of Excel files, not just one.


    Here's a summary of the situation:
    My network administrator had to change the network drive. He copied the old drive onto a new one, and now when I access powerpoint presentations that link charts to Excel, they all point to the old drive which is no longer in use.


    In a nutshell, I need to figure out how to "mass change" the path from \\Mon-file1\data$\Departments\Administration1\ to s:\, or manually change hundreds of links...


    Any idea? Any help would be much appreciated.

  • Re: Change Source for Links to Excel charts from PowerPoint


    If you have this problem where you can't use VBA, try: Home>>Prepare>>Edit links to files. Then change each line to the new file name. It's not necessary to change the cell addresses--PPT retains the original data. I did this for a 60+ slide presentation and it took about 15 min. But I don't have to touch it again until Jan 2013.

  • Re: Change Source for Links to Excel charts from PowerPoint


    Quote from wilsoff;588857

    Try this:


    Your code works great but I have a little problem : When I use it, the former Excel source is reopened for each link to be changed.


    The problem seems to be due to that part of the code : SourceFullName = LinkNew


    I don't understand why. Is there a solution to this ? I am using Office 2010


    Thanks

  • Re: Change Source for Links to Excel charts from PowerPoint


    Quote from daejung;601983

    Your code works great but I have a little problem : When I use it, the former Excel source is reopened for each link to be changed
    Thanks


    Are you sure it's the old file it's opening? I know it will open the new new file for every link. The way around that is to have the new Excel file open before you run the macro. Perhaps try opening old Excel file too. Then at least it should run faster because it won't have keep opening and closing it for every link.

  • Re: Change Source for Links to Excel charts from PowerPoint


    Yes I am sure.


    Is it due to my Powerpoint and Excel version ? I got Office 2010


    Or to Windows 7 (less likely I guess)


    It's not the reason why it opens (old file does open in both cases) but I slightly changed your code to be able to use it when both old and new files are not located in the same folder :



    I also wanted GetOpenFileName(...) to open with a default folder but the following code has no effect (Windows 7 ?) :


    Code
    1. ChDrive ("F:")
    2. ChDir ActivePresentation.Path


    Thanks