Activate Powerpoint from Excel

[SeliM]

Good morning
I have been developing a code to copy charts from excel and paste to PowerPoint from script on this site and the net.

I think I have the script OK to the point of creating a PPT file however the script fails at the point of activating Powerpoint.

I have used two options:
- AppActivate ("Microsoft PowerPoint")
and, - PowPntApp.Activate

The variables I have used are:
Dim newPowerPoint As PowerPoint.Application
Dim activeSlide As PowerPoint.Slide
Dim cht As Excel.ChartObject

Could you advise the correct syntax please. Many thanks.
Mel

 

[Trevor G]

I have tested this and it works. I have set the References to use PowerPoint which then also indicates your structuring PowerPoint features as you code to them.

Sub PppT1()
'Set References to use PowerPoint
'Declare your variables
Dim pptApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim StrTtile As String
'Open PowerPoint
Set pptApp = New PowerPoint.Application
Set PPPres = pptApp.Presentations.Add
pptApp.Visible = True
'Add slide as slide 1 and set focus
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
PPSlide.Select
'memory cleanup
pptApp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pptApp = Nothing

End Sub

 

[SeliM]

Thank you very much Trevor
Have to say the script is well beyond my capacity.

The script works perfectly. I have been able to add code that sets a slide per graph with the title of the graph being slide title however I don't get the graph inserted into PPT.

My code for charts insert is:
'Select each Excel chart and paste to PowerPoint
For Each cht In ActiveSheet.ChartObjects

'Create slide to paste the chart
newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

'Copy charts and paste to PowerPoint as a Picture
cht.Select
ActiveChart.ChartArea.Copy
'activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

Your advice most welcomed.

Mel

 

[Trevor G]

Hi Mel,

The following code should give you the solution you need. I have tested it. Please make sure you set the Reference to use Microsoft PowerPoint from the VBA screen. I have tried to document (Comment) the code to help you.

Sub ExpChatrsToPPT()
'Set Reference to use PowerPoint
'Use the Tools Menu and then References
'Search down the list for Microsoft PowerPoint XX which is the version your using

   'Declare PowerPoint Variables
    Dim PPTApp As PowerPoint.Application
    Dim PPTPres As PowerPoint.Presentation
    Dim PPTSlide As PowerPoint.Slide
    Dim SldIndex As Integer
        
   'Declare Excel Variables - Chart Objects
    Dim Chrt As ChartObject
            
   'Create new PowerPoint Application
    Set PPTApp = New PowerPoint.Application
        PPTApp.Visible = True 'Make it visible
    
   'Create New Presentation
    Set PPTPres = PPTApp.Presentations.Add
    
   'Create an index for the slides.
    SldIndex = 1
    
   'Loop through each Chart in the Activesheet
    For Each Chrt In ActiveSheet.ChartObjects
        
       'Copy the Chart
        Chrt.Copy
        
       'Create a new slide and use blank layout, paste chart on the new slide.
        Set PPTSlide = PPTPres.Slides.Add(SldIndex, ppLayoutBlank)
            PPTSlide.Shapes.Paste
        
       'Increment index to paste the next chart on the new slide.
        SldIndex = SldIndex + 1
    
    Next Chrt
    
End Sub

 

[SeliM]

Morning Trevor
Works perfectly - many thanks
Mel

 

[Trevor G]

pleased to read you have a working solution Mel. Thank you for letting me know.