Solved: Defining Used Cells & Send to PPT

[lostin_space]

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:

[mdmackillop]

Any use?

Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

[lostin_space]

no, that just sends a copy of what in the current cell... but thanks anyhoo...

i've automatically determined where the last populated row / column is, and set that as my print area - instead of printing what's there, i need to automatically with vba send a bmp of that into ppt (does that make sense?)

[mdmackillop]

Range("Print_Area").CopyPicture Appearance:=xlScreen, Format:=xlBitmap

[lostin_space]

:bow: :clap: worked like a charm! many thanks! all i gotta do now is suss out how to re-size the image, without it looking like ants have crawled across the page!