Need help controlling PowerPoint from Excel

[Dave L]

I am trying to automate the transfer of a range of cells to PowerPoint just by clicking a button on the Excel sheet. I have the code below which works great.

Sub CreatePowerPointFromChart()
Const ppLayoutBlank As Integer = 12
Const ppWindowMaximized As Integer = 3
Dim PPTApp As Object
Dim Pres1 As Object
Dim Slide1 As Object

Set PPTApp = CreateObject("PowerPoint.Application")

With PPTApp
.Visible = True
.WindowState = ppWindowMaximized
Set Pres1 = PPTApp.Presentations.Add
End With

Set Slide1 = Pres1.Slides.Add(1, ppLayoutBlank)

' Copy and paste selected range
Range("A1:M40").CopyPicture
PPTApp.ActiveWindow.View.Paste

End Sub

The problem is that the contents of the Excel sheet that are being copied need to be pasted into an 8.5 x 11 PowerPoint slide, not the default 7.5 x 10. I recorded a macro in PowerPoint to change the Page Setup and got the following:

Sub Macro1()

With ActivePresentation.PageSetup
.SlideSize = ppSlideSizeCustom
.SlideWidth = 792
.SlideHeight = 612
.FirstSlideNumber = 1
.SlideOrientation = msoOrientationHorizontal
.NotesOrientation = msoOrientationVertical
End With
End Sub

However, if I put that into my code above I get error messages. This Excel file will be used by clients who will create charts with the Excel file and may want to transfer them to PowerPoint, so I would like this one Excel macro to handle everything.

Any ideas how I can control the page setup in PowerPoint from Excel?

Thanks,

Dave

 

[BrianB]

The principle is that if we are manipulating an external application with Excel code we need to explicitly include the Application Object.

I have not used PowerPoint this way, but from Excel your PowerPoint macro will probably translate as :-

With PPTapp.ActivePresentation.PageSetup 
    .SlideSize = ppSlideSizeCustom 
    .SlideWidth = 792 
    .SlideHeight = 612 
    .FirstSlideNumber = 1 
    .SlideOrientation = msoOrientationHorizontal 
    .NotesOrientation = msoOrientationVertical 
End With

 

[Dave L]

Brian,

That did the trick. Thanks for your help.

Dave