Linking with powerpoing using VBA

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

I'm using Peltier's code from here
http://peltiertech.com/Excel/XL_PPT.html#rangeppt to link my excel worksheet
in with powerpoint.

I want to populate 4 slides from different selections in excel. The problem
I'm having is that I can't figure out the code to move onto the next PPT
slide.

I've tried all variations on this, and i've renamed the slides and replaced
the 2 with the appropriate name, but everytime i get a complie error at
select.

Set PPSlide = PPPres.Slides(2).Select

How can I activate the next slide?
 
In PowerPoint it would be:

Dim theSlide As Slide
'
Set theSlide = ActivePresentation.Slides(2)
theSlide.Select

So in Excel, it should be:

Dim theSlide as PowerPoint.Slide
'
Set theSlide = PPres.Slides(2)
theSlide.Select

HTH,

Eric
 
Hi

I've tried that in excel and I'm getting the error "Slides (Unknown member):
Invalid request". I've renamed the slide using a macro to "Units" and have
replaced the 2 in the code below with units

So...
Dim theSlide as PowerPoint.Slide
'
Set theSlide = PPres.Slides("Units")
theSlide.Select
Click to expand...

I've also tried without the quotation marks around units. Any ideas?

Thanks

Emma
 
The code below worked. It creates a new PowerPoint object, then creates a
new presentation, then selects the second slide in the presentation.

Sub Control_PPT()
Dim PPTApp As PowerPoint.Application
Dim PPres As PowerPoint.Presentation
Dim theSlide As PowerPoint.Slide
'
Set PPTApp = New PowerPoint.Application ' Start up PowerPoint
PPTApp.Visible = msoTrue ' IMPORTANT: Make it
visible!
Set PPres = PPTApp.Presentations.Add ' Create a new presentation
object
PPres.Slides.Add Index:=PPres.Slides.Count + 1, Layout:=ppLayoutBlank
PPres.Slides.Add Index:=PPres.Slides.Count + 1, Layout:=ppLayoutBlank
PPres.Slides.Add Index:=PPres.Slides.Count + 1, Layout:=ppLayoutBlank
'
Set theSlide = PPres.Slides(2)
theSlide.Select
'
' Do other stuff here...
'
' Unload the PowerPoint stuff
'
Set theSlide = Nothing
Set PPres = Nothing
Set PPTApp = Nothing
'
End Sub
 
This version opens an existing PowerPoint file instead of creating a new one,
then adds some slides and selects the second slide. Watch for line wrap in
the code below!

Sub Control_PPT()
Dim PPTApp As PowerPoint.Application
Dim PPres As PowerPoint.Presentation
Dim theSlide As PowerPoint.Slide
Dim PPTFile As Variant
'
PPTFile = Application.GetOpenFilename("PowerPoint Files (*.ppt*),
*.ppt*", , "Select a PowerPoint File to Open", , False)
If (PPTFile = False) Then Exit Sub
'
Set PPTApp = New PowerPoint.Application
PPTApp.Visible = msoTrue
Set PPres = PPTApp.Presentations.Open(PPTFile, msoFalse, , msoTrue)
PPres.Slides.Add Index:=PPres.Slides.Count + 1, Layout:=ppLayoutBlank
PPres.Slides.Add Index:=PPres.Slides.Count + 1, Layout:=ppLayoutBlank
PPres.Slides.Add Index:=PPres.Slides.Count + 1, Layout:=ppLayoutBlank
'
Set theSlide = PPres.Slides(2)
theSlide.Select
'
' Do other stuff here...
'
' Unload the PowerPoint stuff
'
Set theSlide = Nothing
Set PPres = Nothing
Set PPTApp = Nothing
'
End Sub
 
Hi

Thanks for your help on this. It is however not working as I wanted it to.
I want to avoid adding new slides if at all possible since each slide in the
presentation has a particular title that doesnt change. So i want to select
an exisiting slide in the active presentation.

The code below will paste the selection onto the 2nd slide however won't let
me move the shape since the slide isnt selected.

Also as a side issue do you have any ideas how to resize the shape from this
code or run a macro in powerpoint from here which will resize it?

Thanks again

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
' Reference active slide
Set PPSlide =
PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

'Select range
Sheets("PPT 4BLOCKER").Select
Range("c45:m76").Select

' Copy the range as a piicture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

' Paste the range
PPSlide.Shapes.Paste.Select

' Position pasted chart

PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
' Reference active slide
Set PPSlide = PPPres.Slides(2)

'Select range
Sheets("PPT 4BLOCKER").Select
Range("c45:m76").Select

' Copy the range as a piicture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

' Paste the range
PPSlide.Shapes.Paste.Select

' Position pasted chart

PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
 
Hi

I've worked it out :)

Thank you very much for all your help Eric. Now i just need to work out how
to resize the picture :)

Set PPSlide = PPPres.Slides(2)
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex

With PPSlide

' Paste the range
PPSlide.Shapes.Paste.Select

' Position pasted chart

PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

End With
 
Recording a macro in PowerPoint gives this for sizing and positioning:

ActiveWindow.Selection.SlideRange.Shapes("AutoShape 4").Select
With ActiveWindow.Selection.ShapeRange
.ScaleWidth 0.74, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.79, msoFalse, msoScaleFromBottomRight
End With
With ActiveWindow.Selection.ShapeRange
.IncrementLeft 48#
.IncrementTop -6#
End With

You should be able to do that from Excel by referencing the PPT app,
something like "PPApp.Activewindow.Selection...". A little experimenting
will get you there!
 
works perfectly thank you so much.

EricG said:
Recording a macro in PowerPoint gives this for sizing and positioning:

ActiveWindow.Selection.SlideRange.Shapes("AutoShape 4").Select
With ActiveWindow.Selection.ShapeRange
.ScaleWidth 0.74, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.79, msoFalse, msoScaleFromBottomRight
End With
With ActiveWindow.Selection.ShapeRange
.IncrementLeft 48#
.IncrementTop -6#
End With

You should be able to do that from Excel by referencing the PPT app,
something like "PPApp.Activewindow.Selection...". A little experimenting
will get you there!
Click to expand...
 
Back
Top