PDA

View Full Version : Solved: Excel to Powerpoint - Variation on a Theme



lostin_space
07-14-2006, 03:24 AM
Hi All,
i'm having a bit of a problem, posting an automatically selected range of data from Excel to powerpoint...

What i'm trying to do, is give my users a button to press, that'll automatically define all the populated cell on a worksheet, and then copy + paste that data as a bitmap into a PPT presentation... i

here's the code i'm using to automatically define the populated area of my sheet

Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) <> 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

i'm trying to integrate it into this example :-

Public Sub ExportToPowerpoint()
' Set a VBE reference to Microsoft PowerPoint Object Library
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim lastCell As Range
Dim printme As Range

Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) <> 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
Set PPApp = CreateObject("Powerpoint.Application") ' Create new poerpoint presentation
PPApp.Visible = msoTrue
Set PPPres = PPApp.Presentations.Add
Set PPSlide = PPPres.Slides.Add(1, ppLayoutBlank) ' Add new blank slide


' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
' Reference active slide
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

' Copy the range
Selection.Copy 'Appearance:=xlScreen, Format:=xlPicture
' Paste the selection as a picture
PPSlide.Shapes.PasteSpecial DataType:=ppPasteBitmap
PPSlide.Shapes(1).Select
With PPSlide.Shapes(1)
.ScaleWidth 0.8, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.635, msoFalse, msoScaleFromTopLeft
End With

' Align the pasted range
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End If
End Sub

anyone got any ideas? can anyone help??

Cheers

Russ
:banghead:
Edited 7-Aug-06 by geekgirlau. Reason: insert vba tags

shades
08-05-2006, 06:49 PM
Two things:

1. Look at this thread (http://www.vbaexpress.com/forum/showthread.php?t=392) about automating linked Excel range/chart to Powerpoint.

2. Use the vba code tags so that code really looks like code.

[ VBA ]Sub Test()
' Code goes here
End Sub[ /VBA ]
(but without the spaces, so that it will display as follows).

Sub Test()
' Code goes here
End Sub