-
Solved: Defining Used Cells & Send to PPT
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
[VBA]
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
[/VBA]
anyone got any ideas? can anyone help??
Cheers
Russ
-
Any use?
[VBA]
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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?)
-
[VBA]Range("Print_Area").CopyPicture Appearance:=xlScreen, Format:=xlBitmap
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules