Microsoft Office Forums Identify source chart in Excel

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
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
Novice
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.

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

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


All times are GMT -7. The time now is 05:38 AM.


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