lostin_space
07-14-2006, 04:06 AM
Hi All,
i'm having a bit of a problem, defining an automatically selected range of data from Excel and squirting it into powerpoint...
What i'm trying to do, is give my users a button to press, that'll automatically define all the populated cells 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
and 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)
**here's where i need to put my print area stuff & squirt to ppt
' 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:
i'm having a bit of a problem, defining an automatically selected range of data from Excel and squirting it into powerpoint...
What i'm trying to do, is give my users a button to press, that'll automatically define all the populated cells 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
and 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)
**here's where i need to put my print area stuff & squirt to ppt
' 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: