View Single Post
Old 10-09-2019, 08:55 AM
Dr. Demento Dr. Demento is offline Windows 7 32bit Office 2010 32bit
Join Date: Nov 2015
Location: Skipping stones off Charon's Ferry
Posts: 7
Dr. Demento is on a distinguished road
Default 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.

Sub countLinks()

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
Reply With Quote