Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Thread Tools Display Modes
Old 10-09-2019, 08:55 AM
Dr. Demento Dr. Demento is offline Identify source chart in Excel Windows 7 32bit Identify source chart in Excel Office 2010 32bit
Identify source chart in Excel
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

Thread Tools
Display Modes

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
Identify source chart in Excel Create DDL from Excel source BradleyS Word 1 01-30-2013 10:58 PM
Identify source chart in Excel 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:44 PM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd. is not affiliated with Microsoft