Excel VBA to Change PowerPoint Slide Size

paveil

New Member
Joined
Sep 4, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Hi,

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

Regards
 

paveil

New Member
Joined
Sep 4, 2019
Messages
2
Absolutely brilliant - thank you. I had tried the same thing, but was putting ppSlideSizeOnScreen instead of '1'. :)
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
You are welcome, and welcome to the MrExcel board! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,354
Messages
5,443,962
Members
405,257
Latest member
daveyf

This Week's Hot Topics

Top