Update Excel object in PowerPoint

Question

I have a PowerPoint presentation which has Excel tables as objects.

The Excel files are updated once a week. Once Excel files are updated, I open PowerPoint, double-click on each object -> menu Data -> Edit Links -> select all sources -> Update Values.

Is it possible for a macro to find the Excel objects in PowerPoint and update them?

After searching the web I've managed to get the following code which takes me up to the step of double clicking the object, but I don't know how to update links.

Sub update_objects()

    Dim it As String
    Dim i As Integer
    For i = 1 To ActiveWindow.Selection.SlideRange.Shapes.Count
        With ActiveWindow.Selection.SlideRange.Shapes(i)
            If .Type = msoEmbeddedOLEObject Then
                ActiveWindow.Selection.SlideRange.Shapes(i).Select.OLEFormat.DoVerb
            End If
        End With
    Next i
End Sub

In theory, ActivePresentation.UpdateLinks should do it. Have you tried that? — Steve Rindsberg, Jul 19 '16 at 00:25
@SteveRindsberg thanks for advice, I have tried that and it only updates graphs in presentation which are updated anyway when presentation is opened. What I have is actual table from excel which needs to be updated. Thanks, Yuri — Yuri, Jul 19 '16 at 08:21
Do you mean specifically an Excel table (as opposed to a bunch of cells in Excel that happen to look like a table)? I'm not sure how tables behave, but if it's linked cells, then updating links should work. If the range of cells is embedded, then no. The Excel object would have to be activated for any changes to happen. — Steve Rindsberg, Jul 19 '16 at 15:12
@SteveRindsberg thanks for your reply and sorry for confusion. it is a bunch of cells which are linked with a formula to other spread sheets which are updated weekly. These cells are embedded. I've tried inserting object from file and linking it but the problem I get is that one excel sheet has few tables (bunches of cells) and I need to show only one table at a time, but linked object shows all data on that sheet. With embedded object I can resize it so it shows only some parts of the sheet. I've haven't used linked objects before so nee to read more on how to use them. Thanks — Yuri, Jul 20 '16 at 08:53
Instead of using Insert | Object from within PPT, start in Excel. Select the cells you want to link to, right-click, Copy then go to PPT and choose Paste Special | put a check next to Link and choose Excel Worksheet Object (or something like that). — Steve Rindsberg, Jul 20 '16 at 14:19
@SteveRindsberg thanks for your advice, worked exactly as I need it to work. I always learn something new each day! :) Could you advice if there is a way of updating graphs on weekly basis by one data point without going through each graph opening Select Data > Edit Series and increase by one cell? or am I asking to much here? Thank you in advance — Yuri, Jul 22 '16 at 12:46
I imagine there's some way of doing this via VBA or other code @Yuri, but offhand I don't know how to do it. — Steve Rindsberg, Jul 22 '16 at 14:38

score 0 · Answer 1 · answered Dec 07 '16 at 19:10

This code works, but not in all instances. It was written for 2010. Change the Type as needed. 7 is Excel Embedded objects.

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Sub open_and_close_all_objects()
    'This will open and then close all charts and graphs in the presentation to allow them to keep the data
    'Why this is needed is a question to be answered in the future.


    Dim oSH As Shape
    Dim oSl As Slide
    Dim oSheet As Object


    For Each oSl In ActivePresentation.Slides
        For Each oSH In oSl.Shapes

        ActiveWindow.View.GotoSlide oSl.Slideindex
             If oSH.Type = 7 Then
                oSH.Select
                oSH.OLEFormat.Activate

                Call Sleep(1500)

                ActiveWindow.Selection.Unselect
                ActiveWindow.View.GotoSlide oSl.Slideindex
            End If

        Next


     Next
     End Sub

score 0 · Answer 2 · edited Apr 23 '23 at 16:48

0

To use Sleep function, do this:

#If VBA7 Then ' Excel 2010 or later
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else ' Excel 2007 or earlier
Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If

From: https://www.educba.com/vba-sleep/

edited Apr 23 '23 at 16:48

Tyler2P

2,324
26
22
31

answered Apr 17 '23 at 19:58

David D.

1

Update Excel object in PowerPoint

2 Answers2