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!!

---

 

[Worf]

Hello and welcome to the Board!

· If you create the PowerPoint object by linking an Excel range, each time the presentation is opened all objects are updated.
· By right clicking the PowerPoint object and choosing Object/Edit, Excel is activated and the range can be modified. Does this solve your problem? If not, please explain.

 

[brokerbevo]

Hello and welcome to the Board!

· If you create the PowerPoint object by linking an Excel range, each time the presentation is opened all objects are updated.
· By right clicking the PowerPoint object and choosing Object/Edit, Excel is activated and the range can be modified. Does this solve your problem? If not, please explain.

Thanks for the response,

I'm not sure what you mean by linking an Excel range. If you are suggesting that I copy a range of cells from the master spreadsheet, then in the Powerpoint do “Paste Special” --> “Paste Link,” that wouldn't work because that would just paste a refreshable picture that I can’t make modifications to (as stated in my original post).

Regarding you second point, this is what I am trying to avoid -- manually activating each embedded sheet (which equates to like 225 double-clicks each time I need to update the powerpoint).

What I'm trying to find out is if there is any easier/quicker way to auto-update/refresh every embedded spreadsheet within each slide, such as using VBA, etc. Thanks!

 

[Worf]

Hi

Let me try again. Yes, I’m talking about linked objects. I understood that you have two issues:

1) Refreshing
When closing and reopening the presentation, all linked objects are updated, so that would eliminate the need to manually do it.

2) Modifying
Right clicking it will give you the option to edit the object in the original application, Excel in this case. Changes will be reflected on the linked object in PowerPoint. Is this the kind of modification you want?

I can look for an alternative solution for embedded objects, but would like to understand why the above does not fulfill your needs.

 

[brokerbevo]

Hi

Let me try again. Yes, I’m talking about linked objects. I understood that you have two issues:

1) Refreshing
When closing and reopening the presentation, all linked objects are updated, so that would eliminate the need to manually do it.

2) Modifying
Right clicking it will give you the option to edit the object in the original application, Excel in this case. Changes will be reflected on the linked object in PowerPoint. Is this the kind of modification you want?

I can look for an alternative solution for embedded objects, but would like to understand why the above does not fulfill your needs.

Thanks again for the prompt reply. See my response below:
1) Refreshing: Yes, but if I do the "linking route," the power point object is just a picture, and we want to be able to edit the content of the embedded object within the powerpoint that its located

2) Modifying: This is basically what I want to do, the problem is I have 5 embedded object per slide and about 45 slides, thus totalling 225 embedded objects that I have to right-click, and edit the object in order for it to update/refresh, each and every time that I want to update/refresh the powerpoint data -- this takes a LONG time. I would prefer if there were a macro that could do this step for me automatically (i.e. for each embedded object in powerpoint....).

I hope that made sense

 

[Worf]

Hi

This is the VBA equivalent of double clicking every embedded object in the presentation. It may take a while to run with a lot of objects. Tell me if it does what you want.

Sub Edit_Embedded()
Dim sh As Shape, i%
For i = 1 To ActivePresentation.Slides.Count
    For Each sh In ActivePresentation.Slides(i).Shapes
        If sh.Type = 7 Then
            ActiveWindow.ViewType = 1
            Application.Visible = msoTrue
            Windows(1).View.GotoSlide i
            DoEvents
            sh.OLEFormat.DoVerb (1)
        End If
    Next
Next
End Sub

 

[brokerbevo]

Hi

This is the VBA equivalent of double clicking every embedded object in the presentation. It may take a while to run with a lot of objects. Tell me if it does what you want.

Sub Edit_Embedded()
Dim sh As Shape, i%
For i = 1 To ActivePresentation.Slides.Count
    For Each sh In ActivePresentation.Slides(i).Shapes
        If sh.Type = 7 Then
            ActiveWindow.ViewType = 1
            Application.Visible = msoTrue
            Windows(1).View.GotoSlide i
            DoEvents
            sh.OLEFormat.DoVerb (1)
        End If
    Next
Next
End Sub

Wow!!! That is EXACTLY what I needed!! That did the trick perfectly. Sure it takes a long time, but I'll just run it in the morning before the presentation. Thanks again!!!!

 

[Worf]

You are welcome!

 

[ivanova]

hello worf

I hope you can help me about this error

Compile error:
Invalid qualifier

the code invalid is in Window(1).View.GotoSlide I
the code invalid is View

would you like to send to me example file in excel and powerpoint

 

[ivanova]

hello worf

this is my email affandi81@gmail.com

thanks in advance