VBA Excel to PP - use existing presentation?

Dimitris254

Board Regular
Joined
Apr 25, 2016
Messages
139
Hi all,

i'm using VBA in Excel to copy&paste charts as images to a PowerPoint presentation.

I would like to modify the code, so that it's using the existing presentation (which is created after the 1st run) from now on.

My purpose is to update the charts with new ones, while keeping the slide title, subtitle and text boxes untouched. I'm planning to do this by (1) deleting all charts in all slides and then (2) run the code below (most of it copied from Jon Peltier's guide) :
Code:
Private Sub update_charts_PP()

Dim PP_App As PowerPoint.Application    ' declare a PowerPoint Application
Dim PP_Pres As PowerPoint.Presentation  ' declare a PowerPoint Presentation
Dim PP_Slide As PowerPoint.Slide        ' declare a PowerPoint Slide
Dim PP_Shape As PowerPoint.Shape        ' declare a PowerPoint shapes
Dim PP_template As String               ' declare a PowerPoint template
Dim SlideCount As Integer  ' slide counter

' create a new instance of PP
Set PP_App = CreateObject("PowerPoint.Application")
        
' for automation to work, PP must be visible
PP_App.Visible = True
        
' create a presentation
Set PP_Pres = PP_App.Presentations.Open(PP_template)
       
' reference active presentation
Set PP_Pres = PP_App.ActivePresentation


' Add a new slide
SlideCount = PP_Pres.slides.Count
Set PP_Slide = PP_Pres.slides.Add(SlideCount + 1, Layout:=ppLayoutTitleOnly)   ' 11 = ppLayoutTitleOnly
PP_App.ActiveWindow.View.GotoSlide PP_Slide.SlideIndex
    
' select the Excel range where the charts are as an image
wsCHART.Range("A34:L56").CopyPicture Appearance:=xlPrinter, Format:=xlPicture
 
' prevent the paste being attempted before the image conversion is completed
DoEvents
 
' paste, resize and align the image
With PP_Slide.Shapes.Paste
    .LockAspectRatio = msoTrue
    .Width = 550
    .Left = 0
    .Top = 70
End With


End Sub

Thank you in advance :)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
if i re-phrase the whole question, it would go down to this:

if i have a presentation with 10 slides, how do i write in the X slide?

i think the only part of the code that needs to be modified is this:
Code:
SlideCount = PP_Pres.slides.Count
Set PP_Slide = PP_Pres.slides.Add(SlideCount + 1, Layout:=ppLayoutTitleOnly)
PP_App.ActiveWindow.View.GotoSlide PP_Slide.SlideIndex

:confused:
 
Upvote 0

Forum statistics

Threads
1,223,054
Messages
6,169,835
Members
452,284
Latest member
TKM623

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top