Excel VBA to Change PowerPoint Slide Size

[paveil]

Hi All,

I have some code that takes a range from excel, creates a PowerPoint presentation and paste's the range from excel as a picture in PowerPoint - which works. The issue I have now is that PowerPoint opens in Widescreen format and I wanted some code for excel to change the slide size to standard.

I've played around with different code, but also receive errors (various errors). Does anyone have any ideas?

Code is below:

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim mySlide1 As Object
Dim myShape As Object
Dim myShape1 As Object
Dim myShape2 As Object


'Create an Instance of PowerPoint
On Error Resume Next

'Is PowerPoint already opened?
Set PowerPointApp = GetObject(class:="PowerPoint.Application")

'Clear the error between errors
Err.Clear


'If PowerPoint is not already open then open PowerPoint
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")

'Handle if the PowerPoint Application is not found
If Err.Number = 429 Then
MsgBox "PowerPoint could not be found, aborting."
Exit Sub
End If


On Error GoTo 0


'Optimize Code
Application.ScreenUpdating = False

'Create a New Presentation
Set myPresentation = PowerPointApp.Presentations.Add


'Add a slide to the Presentation
Set mySlide = myPresentation.Slides.Add(1, 12) '12 = ppLayoutBlank


'Optimize Code
Application.ScreenUpdating = False

'Copy Range from Excel
Set rng = ThisWorkbook.ActiveSheet.Range("A8:Y122")


'Copy Excel Range
rng.Copy


'Paste to PowerPoint and position

Application.ScreenUpdating = True

mySlide.Shapes.PasteSpecial DataType:=1

Set myShape = mySlide.Shapes(mySlide.Shapes.Count)

'Set Position:
myShape.Left = 0
myShape.Top = 33


' Set Size:
myShape.Height = 400
myShape.Width = 700


' - - - - -


Application.ScreenUpdating = False

 

[ZVI]

Hi,

Use: myPresentation.PageSetup.SlideSize = 1 'ppSlideSizeOnScreen = 1

Regards

 

[paveil]

Absolutely brilliant - thank you. I had tried the same thing, but was putting ppSlideSizeOnScreen instead of '1'.

 

[ZVI]

You are welcome, and welcome to the MrExcel board!