
Tips.Net > ExcelTips Home > Working with Other Programs > Working with Powerpoint > Snapshots of Excel Worksheets for PowerPoint
Summary: If you need to get lots of information from Excel to PowerPoint, the task can be daunting. This tip explains different approaches you can take when you need to get data from here to there. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)
Rebecca Birch has some clients who require that all reports be presented to them in PowerPoint. This presents a huge task, since all of the source data for those reports is available only in Excel. Rebecca was looking about for ideas to make the burden of converting from one to the other just a bit easier--perhaps by taking "snapshots" of worksheet data and placing it into PowerPoint slides.
One solution, if there are not that many snapshots necessary, is to simply do the pasting manually. You can display information in Excel, and then press the PrintScreen key to place a picture of it in the Office Clipboard. Switch to PowerPoint and choose Office Clipboard from the Edit menu. You can then see the contents of the Clipboard and choose what you want pasted into the current slide.
A less repetitive approach would be to link data from the Excel workbook to the slides. You can use Edit | Paste Special (in PowerPoint) to paste linked data. In this way, anytime the data in the workbook is updated, the linked slides will also be updated. Done correctly, this solution carries the possibility of only needing to do your pasting a single time.
If you prefer to take the route of developing macro to do the pasting, check out one developed by Jon Peltier at his Web site:
http://peltiertech.com/Excel/XL_PPT.html#rangeppt
It will take a snapshot of whatever cells are selected, and then paste them into the active slide in PowerPoint. (Obviously, you must have both Excel and PowerPoint open in order to use the macro.)
Further, the macro could be relatively easily modified so that it stepped through a series of named ranges in Excel and pasted the contents of those ranges into specified slides in PowerPoint.
Another macro-based solution is to create a new PowerPoint presentation (from within Excel) that will contain a snapshot of each of the worksheets in the current Excel workbook. The following macro accomplishes this task:
Sub CopyWksToPPT()
Dim pptApp As Object
Dim sTemplatePPt As String
Dim wks As Worksheet
Dim sTargetTop As Single
Dim sTargetLeft As Single
Dim sTargetWidth As Single
Dim sTargetHeight As Single
Dim sScaleHeight As Single
Dim sScaleWidth As Single
Dim iIndex As Integer
'Change these as desired
sTargetTop = 30
sTargetLeft = 60
sTargetWidth = 600
sTargetHeight = 450
sTemplatePPt = "C:\Program Files\Microsoft Office\Templates\Blank Presentation.pot"
iIndex = 1
Set pptApp = CreateObject("Powerpoint.Application")
With pptApp
.Visible = True
.Presentations.Open _
FileName:=sTemplatePPt, Untitled:=msoTrue
For Each wks In Worksheets
wks.Select
.ActiveWindow.View.GotoSlide _
Index:=.ActivePresentation.Slides.Add _
(Index:=iIndex, Layout:=12).SlideIndex
iIndex = iIndex + 1
wks.UsedRange.Copy
.ActiveWindow.View.Paste
With .ActiveWindow.Selection.ShapeRange
sScaleHeight = sTargetHeight / .Height
sScaleWidth = sTargetWidth / .Width
If sScaleHeight < sScaleWidth Then
sScaleWidth = sScaleHeight
Else
sScaleHeight = sScaleWidth
End If
.ScaleHeight sScaleHeight, 0, 2
.ScaleWidth sScaleWidth, 0, 2
.Top = sTargetTop + (sTargetHeight - .Height) / 2
.Left = sTargetLeft + (sTargetWidth - .Width) / 2
End With
Next
.Visible = True
End With
End Sub
Note the area that says "Change these as desired." This contains the specifications of where the pasted snapshot will be within each PowerPoint slide, as well as its height and width. Also included, in the sTemplatePPt variable, is the full path to the template that should be used for the new PowerPoint presentation.
Tip #2378 applies to Microsoft Excel versions: 97 2000 2002 2003
Don't Go in Debt for Christmas! Tired of trying to keep up with the Joneses for Christmas? Want to enjoy the season rather than dread the aftermath? Learn how you can avoid the financial traps that spring up every Christmas.
Check out Top Fifteen Tips for Financing Christmas today!
Thousands of ExcelTips, available for immediate download. Have all the Microsoft Excel info you need, right at your fingertips. (more information...)
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Beauty Tips
Bugs and Pests Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Money Tips
Organizing Tips
Pet Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site