How to export from Excel to PowerPoint

[Guest]

I have found different code to export a Range from Excel to PowerPoint. The
code I have is as follows:

Sub RangeToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library

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

' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
' Reference existing instance of PowerPoint
On Error Resume Next
Set PPApp = GetObject(, "Powerpoint.Application")

' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

' Reference new instance of PowerPoint
If Err.Number <> 0 Then
'Err.Number > 0 if no PP application is active or running. Then I will have
to create one instance:
Set PPApp = CreateObject("Powerpoint.Application")

Set PPPres = PPApp.Presentations.Add

'Add a slide
PPPres.Slides.Add 1, ppLayoutBlank

PPApp.Visible = True
'Activate PowerPoint
AppActivate PPApp.Name
End If
On Error GoTo 0

' Reference active slide
Set PPSlide =
PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

' Copy the Excel range as a picture
Selection.Copy
'Selection.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture DOES NOT DO THE TRICK WITH BIG PICTURES SO I NEED
TO COPY NORMALLY ....

' .. AND PASTE AS A PCITURE!
' Paste the range
PPSlide.Shapes.PasteSpecial DataType:=ppPasteMetafilePicture

'Now I want to select the pasted Range. BUT HOW?
ActiveWindow.Selection.SlideRange.Shapes(1).Select

'Now I want to add a new PP slide and activate that slide to paste another
range: BUT HOW?

'END MACRO

Any help appreciated!!

Martin

P.S. Basically I need to know how to VBA program the PowerPoint actions from
Excel == > so recording in PP and pasting in VBA editor of Excel
unfortunately does not function!

 

[Guest]

Hi Martin,
The first thing you want to make sure of is to have the references for PPT
turned on in you excel workbook.

In the Excel VBA Editor, On the Menu Bar, Go to Tools -> References -> and
check the box next to "Microsoft PowerPoint XX.0 Obect Library"

You might want to also check Microsoft Office XX.0 Object Library"
the XX will depend on the version of Office you are Using ie... 11.0 for
Office 2003, 10.0 for Office XP.

Please post the version of Office you are using as well as any error
messages you are getting.

Hope this helps

Patrick

 

[Brian Reilly, MS MVP]

Here's some code that I copied out of one of my XL workbooks.

The PPT application is already created elsewhere

This pastes the copied chart

Dim lShapesThisSlide As Long
'Count the objects before pasting
lShapesThisSlide =
pptapp.ActiveWindow.Selection.SlideRange.Shapes.Count
With pptapp
.ActiveWindow.View.PasteSpecial (ppPasteOLEObject)
'pasted object is 1 count higher than before and no need to select it,
'just refer to it
.ActiveWindow.Selection.SlideRange.Shapes(lShapesThisSlide +
1).Name = "Chart"
With .ActiveWindow.Selection.SlideRange.Shapes("Chart")
.ScaleHeight 0.94, True ''' Maintain aspect ratio.
.ScaleWidth 0.94, True
.Top = 57.5
.Left = 30.5
End With
End With

As for adding a slide, you already have that in your previous code
sample.

Brian Reilly, PowerPoint MVP

 

[Steve Rindsberg]

' Reference active slide

Set PPSlide =
PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

' Copy the Excel range as a picture
Selection.Copy
'Selection.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture DOES NOT DO THE TRICK WITH BIG PICTURES SO I NEED
TO COPY NORMALLY ....

' .. AND PASTE AS A PCITURE!
' Paste the range

Something like this should work; you want to dim a variable as shape and
get a reference to the pasted shape in it:

Dim ppSh as PowerPoint.Shape
Set ppSh = PPSlide.Shapes.PasteSpecial(ppPasteMetafilePicture)
With ppSh
' do whatever you need to do next
End With

 

[Guest]

Brian:

Here's some code that I copied out of one of my XL workbooks.

The PPT application is already created elsewhere

This pastes the copied chart

Dim lShapesThisSlide As Long
'Count the objects before pasting
lShapesThisSlide =
pptapp.ActiveWindow.Selection.SlideRange.Shapes.Count

I get an error (number 424: Object required). Any ideas what´s wrong? (PS I
do have a reference set to PowerPoint in Excel as suggests PSKelligan

TIA

Martin

 

[Guest]

Hello Steve!

Something like this should work; you want to dim a variable as shape and
get a reference to the pasted shape in it:

Dim ppSh as PowerPoint.Shape
Set ppSh = PPSlide.Shapes.PasteSpecial(ppPasteMetafilePicture)

I get on error here: the copied excel graph does get pasted but then I see
an error 13: the types do not coincide.

Any idea why?

TIA

Martin

 

[Guest]

Forget the errors (I forgot to change pptapp to ppApp in my macro grin). The
final code goes like:


Sub ExportXlGraphSheet2PP()

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

For iCht = 1 To 2

'Could this be written better?
Sheets("Gráfico" & iCht).Select
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy

On Error Resume Next
Set PPApp = GetObject(, "Powerpoint.Application")

' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

' Reference new instance of PowerPoint
If Err.Number <> 0 Then
Set PPApp = CreateObject("Powerpoint.Application")
Set PPPres = PPApp.Presentations.Add

'Add a slide
PPPres.Slides.Add 1, ppLayoutBlank
PPApp.Visible = True
'Activate PowerPoint
AppActivate PPApp.Name
End If
On Error GoTo 0

' Reference active slide
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex

lShapesThisSlide = PPApp.ActiveWindow.Selection.SlideRange.Shapes.Count
With PPApp
.ActiveWindow.View.PasteSpecial (ppPasteMetafilePicture)
' .ActiveWindow.View.PasteSpecial (ppPasteOLEObject) 'I changed this
to prevent a copy of the complete Excel workbook

'pasted object is 1 count higher than before and no need to select it,just
refer to it
.ActiveWindow.Selection.SlideRange.Shapes(lShapesThisSlide + 1).Name
= "Chart"
With .ActiveWindow.Selection.SlideRange.Shapes("Chart")
.ScaleHeight 0.94, True ''' Maintain aspect ratio.
.ScaleWidth 0.94, True
.Top = 57.5
.Left = 30.5
End With
End With ' PPSlide
Next 'iCht Excel sheet


End Sub

'END MACRO

Thanks for all the help!!!!!!!!!! I greatly appreciate this. This problem
had me occupied for a couple of months!

Cheers

Martin

P.D.

 

[Steve Rindsberg]

Hello Steve!


I get on error here: the copied excel graph does get pasted but then I see

v> an error 13: the types do not coincide.

Any idea why?

My mistake, sorry. Paste and PasteSpecial return a shaperange, not a shape.

Each item in the shaperange is a shape and there'll only be one item pasted, so
this'll behave better:

Set ppSh = PPSlide.Shapes.PasteSpecial(ppPasteMetafilePicture).Item(1)