Excl objects pasted into VBA as object - Macro to list out the addresses

[BuJay]

I have a bunch of charts linked to excel from within powerpoint.

Can someone provide a macro that would list out the full addresses of each linked object, i.e., all of the source addresses? Printing to the immediate window is sufficient - I am just thinking that there is an improperly linked object somewhere and cannot expand the address string in the powerpoint edit links window. (See image)

 

[Domenic]

The following macro, which needs to be run from within PowerPoint, assumes that the presentation containing your linked charts is the active presentation. It loops through each slide within the active presentation. And for each slide, it loops through each shape within the slide. And if a shape contains a linked chart, it prints its source to the Immediate Window.

Option Explicit

Sub ListLinkedChartSources()

    Dim currentSlide As Slide
    Dim currentShape As Shape
   
    For Each currentSlide In ActivePresentation.Slides
        For Each currentShape In currentSlide.Shapes
            If currentShape.Type = msoChart Then
                If currentShape.Chart.ChartData.IsLinked Then
                    Debug.Print "Slide " & currentSlide.SlideIndex, currentShape.Name, currentShape.LinkFormat.SourceFullName
                End If
            End If
        Next currentShape
    Next currentSlide


End Sub

Hope this helps!

 

[BuJay]

The following macro, which needs to be run from within PowerPoint, assumes that the presentation containing your linked charts is the active presentation. It loops through each slide within the active presentation. And for each slide, it loops through each shape within the slide. And if a shape contains a linked chart, it prints its source to the Immediate Window.

Option Explicit

Sub ListLinkedChartSources()

    Dim currentSlide As Object
    Dim currentShape As Object
   
    For Each currentSlide In ActivePresentation.Slides
        For Each currentShape In currentSlide.Shapes
            If currentShape.Type = msoChart Then
                If currentShape.Chart.ChartData.IsLinked Then
                    Debug.Print "Slide " & currentSlide.SlideIndex, currentShape.Name, currentShape.LinkFormat.SourceFullName
                End If
            End If
        Next currentShape
    Next currentSlide


End Sub

Hope this helps!

Thank you! I will test it first thing tomorrow! Can’t wait!

 

[BuJay]

The following macro, which needs to be run from within PowerPoint, assumes that the presentation containing your linked charts is the active presentation. It loops through each slide within the active presentation. And for each slide, it loops through each shape within the slide. And if a shape contains a linked chart, it prints its source to the Immediate Window.

Option Explicit

Sub ListLinkedChartSources()

    Dim currentSlide As Object
    Dim currentShape As Object
   
    For Each currentSlide In ActivePresentation.Slides
        For Each currentShape In currentSlide.Shapes
            If currentShape.Type = msoChart Then
                If currentShape.Chart.ChartData.IsLinked Then
                    Debug.Print "Slide " & currentSlide.SlideIndex, currentShape.Name, currentShape.LinkFormat.SourceFullName
                End If
            End If
        Next currentShape
    Next currentSlide


End Sub

Hope this helps!

Strange - when I execute the macro, nothing happens. No output or anything. I did ensure the presentation was active and saved as macro enabled but nothing...seems like the code makes sense but a little confused as to why nothing is happening.

 

[Domenic]

Are your linked charts placed within placeholders?

 

[BuJay]

I copy a chart from excel and paste into powerpoint as a linked excel object.

So, if I then go to the Info menu under the File menu, I can see some of the links. I am trying to get powerpoint to print out all of these links using VBA.

Make sense?

 

[Domenic]

Since the macro didn't print any results to the Immediate Window, I have to assume that you've copied and pasted your charts into content placeholders. Does this help?

Option Explicit

Sub ListLinkedChartSources()

    Dim currentSlide As Slide
    Dim currentPlaceHolder As Shape
   
    For Each currentSlide In ActivePresentation.Slides
        For Each currentPlaceHolder In currentSlide.Shapes.Placeholders
            If currentPlaceHolder.HasChart Then
                If currentPlaceHolder.Chart.ChartData.IsLinked Then
                    Debug.Print "Slide " & currentSlide.SlideIndex, currentPlaceHolder.Name, currentPlaceHolder.LinkFormat.SourceFullName
                End If
            End If
        Next currentPlaceHolder
    Next currentSlide


End Sub

 

[BuJay]

It is very strange and i am sure there is an obvious answer that I am missing. I do not know what placeholders are, so I doubt I am using them. I did step through the macro in the VBA editor and it looks like it is executing correctly....it's just that nothing is being printed to the immediate window. So, I am wondering if the macro is referencing the objects on the slides as the correct object type. If I move to a slide where I have a linked chart and right click on the linked chart, it says "Linked Macro-Enabled Worksheet Object". Would the macro recognize that object as a "chart"?

 

[Domenic]

In that case, try the following instead...

Option Explicit

Sub ListSourcesForLinkedObjects()

    Dim currentSlide As Slide
    Dim currentShape As Shape
    
    For Each currentSlide In ActivePresentation.Slides
        For Each currentShape In currentSlide.Shapes
            If currentShape.Type = msoLinkedOLEObject Then
                Debug.Print "Slide " & currentSlide.SlideIndex, currentShape.Name, currentShape.LinkFormat.SourceFullName
            End If
        Next currentShape
    Next currentSlide

End Sub

Hope this helps!

 

[BuJay]

In that case, try the following instead...

Option Explicit

Sub ListSourcesForLinkedObjects()

    Dim currentSlide As Slide
    Dim currentShape As Shape
   
    For Each currentSlide In ActivePresentation.Slides
        For Each currentShape In currentSlide.Shapes
            If currentShape.Type = msoLinkedOLEObject Then
                Debug.Print "Slide " & currentSlide.SlideIndex, currentShape.Name, currentShape.LinkFormat.SourceFullName
            End If
        Next currentShape
    Next currentSlide

End Sub

Hope this helps!

Thank you so much Domenic - appreciate you seeing this through with me and not getting too frustrated. I'll try to figure out why this worked and the previous versions did not - but - alas - this worked! Thank you very much!