Excel - PowerPoint VBA

[Rob]

I want to copy and paste some charts from Excel in to PowerPoint, first I
group the Chart and its title in Excel, then I want to open a specific
PowerPoint file, then select the page in the file I want the chart, then
paste it in as a picture. The problem I have is selecting the specific page
in the presentation. I use the following code to open the PowerPoint
presentation but I can not get it to change to the right page.
Also once the fiel is open I want to go back to Excel and select another
chart and copy that across to another page within the Powerpoint file without
having to close it and re-open it.
Can anyone help please

Sub OpenPPT()

Dim objPPT As Object
Dim objPres As Object


Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPres = objPPT.Presentations.Open("H:\Temporary\Macro
Developer-Assumption Pack.ppt")
Windows.Item(Index:=1).Activate
End Sub

 

[Barb Reinhardt]

Make sure you define an objXL (similar to PowerPoint) so that you can go
back and forth with it. I was spinning my wheels with something because I
forgot to add the application object to a function yesterday. I suspect you
have code already to copy the charts, what is it you need to know. Post your
code if you have something.

 

[Rob]

Hi Barb, I have a problem with selecting the right page in the Powerpoint
presentation to paste the chart in to. Also how do I return to Excel once in
Powerpoint ? I do not have any code as this is the bit I am struggling with

 

[Barb Reinhardt]

I'm probably going to be posting several replies, so bear with me.

In PowerPoint, I've changed the Slide name (similar to the idea of worksheet
code names in Excel) so that I can access the slide without worrying if
someone has changed the title on the slide. Note that if someone cuts and
pastes the slide, the name will go away.


Here code to display the slide names in the active presentation

Private Sub DisplaySlideName()
'
'Displays the slide index (sequence the slide is in the presentation) and
'the slide name. This is used to identify the slide names for each page of
'the presentation
'.SlideShowTransistion.Hidden = 0 when the slide is not hidden.
'
Dim mySlide As PowerPoint.Slide
Set myPPT = ActivePresentation

Debug.Print "Slide Index", "Slide Name"
For Each mySlide In myPPT.Slides
With mySlide
Debug.Print .SlideIndex, .Name, .SlideShowTransition.Hidden
End With
Next mySlide

End Sub

Here is code to change the slide names. I did this slide by slide the first
time so I never wrote it for multiple slides.

Sub RenameSlideName()
Dim sOldSlideName, sNewSlideName As String
'Used to rename the Slide names from Slide1, Slide2, to something
' more meaningful. The Slide names match the sheet code named
' in the paired excel workbook

Set myPPT = ActivePresentation

sOldSlideName = "Slide68" '<~~ change as needed
sNewSlideName = "Program_Summary_Sheet" '<~~Change as needed
myPPT.Slides(sOldSlideName).Name = sNewSlideName

End Sub

When you want to do something with the slide, do something like this

For each mySlide in myPPT.Slides
if mySlide.Name = "YourNewName1" then

elseif mySlide.Name = "YourNewName2" then

end if
next mySlide

What exactly do you want to do with each slide? Come back with specifics on
one slide and we can go from there. I'm actually working on something
integrating Excel with Powerpoint, so probably can pull some of my code
directly. I'm having to work on it because Excel 2007 doesn't behave the
same way as Excel 2003 as far as copying and pasting is concerned.

HTH,
Barb Reinhardt

 

[Rob]

Barb, Thanks for spending your time on this query. Here is the only coding I
have so far.

Sub OpenPPT()
Windows("Longrange Scenario Comparison BO Export.xls").Activate
ActiveSheet.Shapes.Range(Array("Chart 3", "Rectangle 43")).Select
Selection.ShapeRange.Group.Select
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Selection.Copy

Dim objPPT As Object
Dim objPres As Object


Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPres = objPPT.Presentations.Open("H:\Temporary\Macro
Developer-Assumption Pack.ppt")
Windows.Item(Index:=1).Activate

'I then want to be able to select a page within the PowerPoint
presentation
'and then paste in the the chart 3 and rectangle 43 I have just grouped
'using the code below'

ActiveWindow.Selection.SlideRange.Shapes("Picture 5").Select
ActiveWindow.Selection.ShapeRange.Align msoAlignTops, True
ActiveWindow.Selection.Unselect
End Sub


' I then need to go back to the Excel sheet and select another chart and
rectangle
' group these and then go back to the Powerpoint presentation select
another
' page and then paste the new items in.
End Sub

 

[Barb Reinhardt]

Fortunately, I could pull from code I already had.

If I were you, I'd create a SUB to find the slide and find the cells to put
in. Can you define it in a way that can be done this way?

I'd probably do something like this

Sub FindSlideAndRange(mySLide as PowerPoint.Slide, myWS as Excel.WOrksheet,
myRangeName as string)
Dim myRange as Excel.Range
'How do you determine which slide is the one you want? Let me know and I
can give you some ideas

on error resume next
Set myRange = myWS.Range(myRangeName)
on error goto 0

'Then copy the range and paste into the slide.

End Sub
if not myrange is nothing

 

[Lonnie M.]

Rob, to go back to Excel try the following:

Application.ThisWorkbook.Activate

For opening PowerPoint:

‘Declare the PowerPoint Object
Private PPPres As PowerPoint.Presentation

‘Function to Open PowerPoint/Slide
Private Sub openPPT()
‘Use whatever version number you are using
Set PPApp = CreateObject("Powerpoint.Application.11")
PPApp.Activate
PPApp.Presentations.Open ThisWorkbook.Path & "\Template
\template.ppt"

With PPApp.ActivePresentation
‘Skip this if you don’t want to change the name/location
.SaveAs ThisWorkbook.Path & "\NewName.ppt"
End With
End Sub

To return to PowerPoint:

PPApp.Activate



HTH—Lonnie M.

 

[Rob]

Sorry it's taken me so long to reply, I do not have named slides in my
PowerPoint presentation. it is just the default i.e. slide 1, slide 2 etc

 

[Rob]

I eventually went with this coding
Windows("Longrange Scenario Comparison BO Export.xls").Activate
ActiveSheet.Shapes("Rectangle 55").Select
ActiveSheet.Shapes.Range(Array("Rectangle 55", "Chart 56")).Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

Set PPApp = CreateObject("PowerPoint.Application")
PPApp.Visible = True
Set PPPres = PPApp.Presentations.Open("H:\Temporary\Longrange
Basic\Comparison Pack 2.ppt")
'Goto the relevant slide in the presentation
Set PPSlide = PPApp.ActivePresentation.Slides(7)
PPApp.ActivePresentation.Slides(7).Select
PPSlide.Select
PPSlide.Shapes.Paste.Select
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignTops, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True

Thank you for all your help

 

[Rob]

This coding came from Jon Peltier. Here is the shortcut
http://peltiertech.com/Excel/XL_PPT.html