How Do I Quickly Re-filter and Export Camera Tool Views to PowerPoint Slides?

What the heck is Excel Camera Tool?

Excel’s camera tool is a pretty handy button that allows you to make interactive pictures of your data!

It comes especially handy when you need to copy Excel data that falls in non-adjacent ranges (since the camera tool generates an interactive picture, outline grouping can be used to hide the non-relevant columns/rows between the needed ranges)

Its interactivity also serves a great purpose when the numbers in your desired Excel range are subject to change depending on different scenarios.

Last but not least, using an interactive picture prevents any glitches that may occur with “hard-coded” ranges following the insertion/deletion of rows/columns.

You can activate the tool via File –> Options –> Customize Ribbon –> All Commands –> Add

How do I code it?

Sub Select_Actual()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, 2014
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim Msg As Integer, Ans As Integer
Dim Sh As Shape
Dim PPApp As PowerPoint.Application
Dim PPpres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim Country As Range
Dim Cell As Range
'Create a PP application and make it visible
Set PPApp = New PowerPoint.Application
PPApp.Visible = msoCTrue
'Open the presentation you wish to copy to
Set PPpres = PPApp.Presentations.Open("C:\Users\Angelina\Desktop\Finance Package.pptm")
'**********************************************************************************************
'prevent PowerPoint from losing focus and returning
'"shapes (unknown member) invalid request. the specified data type is unavailable"
'- Run-time error -2147188160 (80048240):View (unknown member) error
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PPApp.Activate
PPApp.ActiveWindow.ViewType = ppViewNormal
PPApp.ActiveWindow.Panes(2).Activate 'standard ppt view
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set Country = ActiveSheet.Range("G7")
'***************************
For Each Cell In ActiveSheet.Range("BW8:BW24")
Country = Cell.Value
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Selection.Copy
Select Case Country
Case "Europe": PPpres.Slides(2).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "UK": PPpres.Slides(6).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "Germany": PPpres.Slides(10).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "Iberia": PPpres.Slides(14).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "Italy": PPpres.Slides(18).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "France": PPpres.Slides(22).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "BEL": PPpres.Slides(26).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "MEMA": PPpres.Slides(30).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "CEE": PPpres.Slides(34).Shapes.PasteSpecial ppPasteEnhancedMetafile
Case "Russia": PPpres.Slides(2).Shapes.PasteSpecial ppPasteEnhancedMetafile
End Select
Next Cell
PPpres.Save
PPpres.Close
End Sub

As you might notice, the code above looks pretty similar to the ones we’ve already used for:

The major difference is that Excel considers interactive pictures generated through the Camera tool as shapes. Hence, they are represented through the following line in the VBA code:

ActiveSheet.Shapes.Range(Array("Picture 1")).Select

As always, don’t forget to activate the Microsoft PowerPoint Object Library in Excel VBE environment in order to enable the interaction between Excel and PowerPoint

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.