Using VBA to copy a Chartsheet to Powerpoint

[MMD]

I have written code for users to specify a worksheet and range or named range
that is then copied to PowerPoint Slides.

For example: If a Chart is on a Sheet named "Revenue" and in cells B4:T64,
the user just adds "Revenue" and "B4:T64". It works the same if the range of
cells are named.

This works fine for Charts embedded in a worksheets but if a Chart is on a
Chartsheet I don't have a range or name to reference. I thought it was just
Chart1, Chart2, etc. but that doesn't seem to work.
Thanks!

 

[Lonnie M.]

Here is an example that I use...

Option Explicit

Private PPApp As PowerPoint.Application
Private PPPres As PowerPoint.Presentation
Private PPSlide As PowerPoint.Slide
Private PPShape As PowerPoint.Shape
Private sCnt&
Private chartWS As Chart
Private chartWB, dataWB As Workbook

Public Sub pptMacro()

Dim CheckStr$, dataDir$

Set chartWB = Application.ThisWorkbook
Set chartWS = ActiveSheet
chartWB.Sheets(chartWS.Name).CopyPicture Appearance:=xlPrinter,
Format:=xlPicture
Call openPPT
Call paste2PPT
Call closePPT
ThisWorkbook.Activate
Sheets(chartWS.Name).Activate
Exit Sub

no_data:

Call closePPT
End Sub


Private Sub openPPT()
Set PPApp = CreateObject("Powerpoint.Application.11")
PPApp.Activate
PPApp.Presentations.Open ThisWorkbook.Path & "\Template
\template.ppt"

With PPApp.ActivePresentation
.SaveAs ThisWorkbook.Path & "\the_new_file_name.ppt"
End With
End Sub

Private Sub closePPT()
'set to normal view before save/close
PPApp.ActiveWindow.ViewType = ppViewNormal
With PPApp.ActivePresentation
.Save
.Close
End With

PPApp.Quit

Set PPShape = Nothing
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub


Private Sub paste2PPT()
' Paste chart
With PPApp.ActiveWindow
.ViewType = ppViewSlide
.View.Paste
End With

' Align pasted chart
sCnt = PPApp.ActiveWindow.Selection.SlideRange.Shapes.Count
Set PPShape = PPApp.ActiveWindow.Selection.SlideRange.Shapes(sCnt)
PPShape.Select
PPShape.ZOrder msoSendToBack 'by sending to the back it now
becomes index:=1

PPApp.ActiveWindow.Selection.Unselect

End Sub




HTH—Lonnie

 

[MMD]

This is good but it doesn't quite get me there. A user may have a wb with
many sheets in it but they only need to pull info from only some of the
sheets.
My utility allows them to specify which sheet and which cells to grab using
the Tab name and a range of data they want. They specify this data in the
utility before clicking on a button to execute the grab and paste. The code
reads this info and copies it to PowerPoint.
But currently they cannot use it for Chartsheets that are seperate tabs but
do not contain the usual worksheet grid.