Microsoft Office Forums - View Single Post

Dr. Demento · 10-09-2019, 08:55 AM

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