Auto-Refresh Embedded Spreadsheets in Power Point

[brokerbevo]

Hello there!! New user, long time listener, posting my first question!!

---

G<ins>oal/Issue</ins>:

• I’m trying to auto-update/refresh several embedded spreadsheets in a PowerPoint file in the fastest way possible

T<ins>he Scenario</ins>:

• I have about 45 slides with about 5 embedded spreadsheets per slide
• The embedded spreadsheets are standard spreadsheet objects:
  • in PowerPoint: “Insert” --> ”Object” --> “Create New” --> “Microsoft Excel Sheet”
• These sheets have formulas in them that reference a master spreadsheet using UNC pathing.<ins></ins>

<ins>For example</ins><ins></ins>:
"=HLOOKUP(D1,'\\nw\data\LTD\[Workload.xlsx]Transfer'!$C$2:$ALM$23,3,FALSE))"
​

(in which “Workload.xlsx” is the “master” spreadsheet)
​

• In the example above, the vale for “D1” would be different for each of the 45 slides, thus referring to different cells/ranges in the master spreadsheet (via the HLOOKUP)

T<ins>he Problem</ins>:

• Anytime the master spreadsheet gets updated, I have to go in and double-click and activate each embedded sheet (45 slides X 5 spreadsheets = 225 times!!!).
• The problem is further complicated by the fact that I can’t just copy a range of cells from the master spreadsheet, do “Paste Special” --> “Paste Link,” because that would just post a refreshable picture that I can’t make modifications to.

Q<ins>uestion</ins>:
Is there any easier/quicker way to auto-update/refresh every embedded spreadsheet within each slide, such as using VBA, etc.? I’m open to any and all suggestions. Thanks!!

---

 

[ivanova]

hello brokerbevo
would you like to share your file have success, cause I have tried the code, but still have error
Thanks & Regards
this is my email affandi81@gmail.com

 

[Worf]

Below, a slightly modified code and a link to my test presentation. If you have many embedded objects, remove the message box inside the loop.

' PowerPoint module
Sub Edit_Embedded()
Dim sh As Shape, i%, j%
j = 0
For i = 1 To ActivePresentation.Slides.Count
    For Each sh In ActivePresentation.Slides(i).Shapes
        If sh.Type = 7 Then
            j = j + 1
            MsgBox "Updating object #" & j
            ActiveWindow.ViewType = 1
            Application.Visible = msoTrue
            Windows(1).View.GotoSlide i
            DoEvents
            sh.OLEFormat.DoVerb (1)
        End If
    Next
Next
MsgBox j & " objects were updated."
End Sub

https://www.dropbox.com/s/lljhhi5kulepqfm/test.pptm?dl=0