Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

run PowerPoint macro on open

Status
Not open for further replies.

remeng

Technical User
Jul 27, 2006
519
US
Hi,

I've done a fair bit of research and it looks like there isn't a direct way to have a PP macro run when it is opened. When you add a PivotTable chart into a presentation from an exterior Excel document, it will live update if the Excel document PivotTable changes. Once you close the PowerPoint presentation, the link stops working even if you set it up in PowerPoint to automatically update. This is all done without VBA and just normal PP settings.

Since it doesn't update after the re-open, I found the following code that works. It will automatically re-connect the two files together and allow the pivot chart to update live again.

Is there a way with both files loaded to SharePoint or other method that can be used so the pivot chart is updated live in the PP file?

Also, if I embed a PivotTable, it won't update if the source in the Excel document updates. Is there a way to allow it to auto update the filters and sorts?

Code:
Sub test()

    Dim pptChart As Chart
    Dim pptChartData As ChartData
    Dim pptWorkbook As Object
    Dim sld As Slide
    Dim shp As Shape

    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If shp.HasChart Then
                Set pptChart = shp.Chart
                Set pptChartData = pptChart.ChartData
                pptChartData.Activate
                shp.Chart.Refresh
            
                On Error Resume Next
                On Error GoTo 0

            End If
        Next
    Next

    Set pptWorkbook = Nothing
    Set pptChartData = Nothing
    Set pptChart = Nothing


End Sub

Thanks for the help.

Mike
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top