# Auto-Refresh Embedded Spreadsheets in Power Point



## brokerbevo (Oct 22, 2015)

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 (Oct 24, 2015)

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 (Oct 25, 2015)

Worf said:


> 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 (Oct 26, 2015)

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 (Oct 26, 2015)

Worf said:


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




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 (Oct 27, 2015)

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 (Oct 27, 2015)

Worf said:


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



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 (Oct 29, 2015)

You are welcome!


----------



## ivanova (Jul 20, 2017)

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 (Jul 20, 2017)

ivanova said:


> hello worf
> 
> this is my email affandi81@gmail.com
> 
> thanks in advance


----------



## brokerbevo (Oct 22, 2015)

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 (Jul 21, 2017)

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 (Jul 22, 2017)

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


----------

