#1
|
|||
|
|||
Identify source chart in Excel
I've constructed a macro that will identify various aspects (source, location, size, etc.) of embedded objects so that I have a record for when the links are inevitably broken. Everything is fine except I'm trying to identify which chart in Excel was used on a specific slide (one chart per slide).
When the embedded object is a copied range, the term .LinkFormat.SourceFullName provides everything: the full path, workbook name, worksheet, and range of the object source. However, when I attempt to identify the source of a chart, I only get the path and workbook name. Here's the abbreviated code I'm using. Ignoring that the output is an array, the term I want to use is arr(8) but I have no idea how to identify, from PowerPoint, the source chart on each slide and which chart it is in Excel (by either name or index number). Any thoughts? Thanks y'all. Code:
Sub countLinks() ' https://stackoverflow.com/questions/26675580/powerpoint-vba-to-update-links-on-excel-files-open-by-someone-else-without-alert dim pres as Presentation Set pres = ActivePresentation Dim sld As Slide Dim ocust As CustomLayout Dim shp As Shape Dim cntr As Long, _ ptInch As Long cntr = 0 ptInch = 72 Dim arr As Variant ReDim arr(1 To 10, 0 To cntr) ' ~~ Loop Through Each Slide in Presentation to break embedded links For Each sld In pres.Slides For Each shp In sld.Shapes If shp.HasChart = True Then cntr = cntr + 1 ReDim Preserve arr(1 To 10, 1 To cntr) arr(1, cntr) = cntr arr(2, cntr) = sld.Name arr(3, cntr) = shp.Name arr(4, cntr) = shp.Left / ptInch arr(5, cntr) = shp.Top / ptInch arr(6, cntr) = shp.Height / ptInch arr(7, cntr) = shp.Width / ptInch arr(8, cntr) = shp.Chart.Name arr(9, cntr) = IIf(shp.LockAspectRatio, "True", "False") arr(10, cntr) = shp.LinkFormat.SourceFullName End If Next shp Next sld End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can we update Chart in PPT based on a table in the excel and the table is part of the Chart Data | nareshj | PowerPoint | 0 | 07-12-2018 02:29 PM |
Identify slide library source | drewman222 | PowerPoint | 0 | 07-13-2015 11:32 AM |
Create DDL from Excel source | BradleyS | Word | 1 | 01-30-2013 10:58 PM |
How to dynamically link imbedded Excel chart with source data from Word | TBoz | Excel | 1 | 07-06-2012 05:53 PM |
C# API to identify the uncommitted changes in Excel and Word document? | althafuddeen | Excel | 0 | 04-06-2010 07:40 AM |