Mikkel approached me to write about the new charts that Microsoft introduced in Excel 2016, and he agreed that we should write some posts together.In the first half of this post, Spreadsheeto will specifically talk about Microsoft’s new Waterfall Chart.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. I took screen shots of the edit links box after I ran the macro to show you what I am seeing. I do it by going to excel, clicking on the chart, copying, then pasting it into the powerpoint slide. Screenshot: Edit 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.At present these links refer to an old version of the excel file in a specific file locations. If I were trying to do it I would use this logic: read the whole name into a whole String find the position of the open square bracket in whole String copy everything upto and including the square bracket into a new variable, start String find the position of the close square bracket in whole String copy the close square bracket and everything after it into end String then concatenate the parts with: whole String = start String & Excel File & end String You may have to play with Excel File to get it to just give you just the filename without the full path - possibly with yet another variable, say Excel File Short Good luck, and Merry Christmas.When Q exports a table or chart to Power Point and Excel it scans the documents to see if the code is contained within the document and, if it is found, the option of updating is provided.
Kasper and Mikkel are very enthusiastic about Excel, they have written a number of tutorials on the Spreadsheeto Blog, and they offer a comprehensive seven-part free training course on Excel.
Ideally having a window come up that prompts the users to point to the excel file from which the powerpoint should update everytime. Sub modifylinks() Dim Excel File Dim exl As Object Set exl = Create Object("Excel. Not all of the OLE Objects in my Power Point are Excel charts (some are direct references to cells) which means they don't have the brackets which seems to be causing a problem.
For your reference I am a complete novice when it comes to VBA code, I begged, borrowed, and stole the code that I referenced. we must've used different methods to get the charts into powerpoint.
The following options are then provided: In order for a document to be updated, Q needs to know that you wish to export to that document.
This is done via the steps described in Changing the document being exported to.