Automating Excel via PowerPoint - Locals window not exposing full Object model (ie Linksources)

Question

background

I am running code (from PowerPoint) that:

Loops through every slide in a presentation.
Checks each shape to determine if it is a chart.
If it is a chart, activate the underlying Excel worksheet, and then change the links in this underlying file to a new source.

I note that the links to excel do not exist at the overall PPT level [viaInfo], they are deliberately linked to each chart so that the presentation can be edited without access to the source excel file.

The code works - broadly.

There is an ongoing error (code running fine now) that I think goes to network and memory stability (fails after around 15 charts), and I am looking to turn off screenupdating as per Turn off screenupdating for Powerpoint.

question

All the charts I access are linked to other workbooks. Yet when the Excel workbook is exposed to PowerPoint the Linksources are not shown in the Locals window even though the code processes each link (image below shows the link exists)

I flipped the automation to access the PowerPoint pack from Excel, same result. No Linksources.

Why would the full object model not also be available in the Locals window when automating PowerPoint with Excel?

Is this a localised glitch I have stumbled over, or is it a broader issue?

The picture below shows the code itearying over the links (ppl variable, but the xlWB variable has no Linksources).

code

Sub FastUpdate()

    Dim sld As Slide
    Dim shp As Shape
    Dim pptchrt As Chart
    Dim pptChrtData As ChartData

    Dim xlWB As Excel.Workbook
    Dim lngStart As Long
    Dim strNew As String
    Dim strMsg As String

    Dim ppl As Variant

    On Error GoTo cleanup
    'set start position manually 
    'lngStart = 34
    If lngStart = 0 Then lngStart = 1

    'call custom function for user to pick file
    'strNew = Getfile
     strNew = "S:\Corporate Model\05 RSM submissions\05 May 2016\02 Checked RSMs\VFAT\Australia\Australia - Valuation and Financial Analysis template.xlsx"


        For Each sld In ActivePresentation.Slides
            If sld.SlideIndex >= lngStart Then
                For Each shp In sld.Shapes
                    If shp.HasChart Then
                    Set pptchart = shp.Chart
                    Set pptChrtData = pptchart.ChartData
                    'open underlying excel file - doesn't just activate chart
                    pptChrtData.Activate
                    '
                    Set xlWB = pptChrtData.Workbook

                    'loop through all links
                    For Each ppl In xlWB.LinkSources
                        strMsg = strMsg & SlideNumber & " " & pptchart.Name & vbNewLine
                        xlWB.ChangeLink ppl, strNew
                    Next

                    xlWB.Close True
                    Set xlWB = Nothing
                    End If
                Next shp
            End If
        Next sld

cleanup:
Set xlWB = Nothing
If Err.Number <> 0 Then MsgBox Err.Description, vbCritical
If Len(strMsg) > 0 Then MsgBox strMsg, vbOKOnly, "Completed"
End Sub

The screenshot is too small for me to read what you're seeing there. Can you repost a link at full res? — Steve Rindsberg, Jun 02 '16 at 15:51
Much better. I don't have a real answer but I'd guess that what you're seeing is because you don't have a full implementation of Excel in these charts created in PPT (and in fact when they were first introduced in Office 2007, there was nearly no object model at all exposed. So it may just be one of those "Why implement something that hardly anyone will notice or use instead of something that users are screaming for?" things. — Steve Rindsberg, Jun 03 '16 at 02:41
@SteveRindsberg I think you are probably right - although I didn't notice anything else missing, and of course the code using the link still actually works. So still a mystery to me. I will take this up with MSFT. Cheers — brettdj, Jun 03 '16 at 02:56
`LinkSources` is a method (not property), that's why you don't see it in Locals window. — BrakNicku, Jun 04 '16 at 06:53
@BrakNicku OMG - I've written so much code to do with Links yet I'd missed that. Pls post as answer to collect the 100 rep. — brettdj, Jun 04 '16 at 07:05

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

Locals and Watch windows show properties of objects. List of properties of Workbook object can be found here.

LinkSources is a method with optional Type parameter.

If you want to debug LinkSources you can add it to Watch window:

or save return value to local variant variable to see it in Locals window.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 04 '16 at 07:38

BrakNicku

5,935
3
24
38

Automating Excel via PowerPoint - Locals window not exposing full Object model (ie Linksources)

background

question

1 Answers1