Solved: paste excel range in powerpoint slide

[riniheijnen]

Good evening,

I am trying to copy an excelrange to a powerpointslide. Whenever I put this action into one macro everything works ok but if I put this action into a separate macro by means op pushing a button it won't succeed and gives an error:

"error -2147188160(80048240) while executing: Shapes(unknown member) : Invalid request. The specified data type is unavailable".

This error happens in the line where I paste de range in the active slide. Can anybody tell me why I get this error and what I need to change to get it working. Thanks for all suggestions.

Option Explicit

Sub tv_programma_zaterdag()
' tv_programma_zaterdag Macro kopieren range

'over here is some formatting which result in a range Realusedrange.

Realusedrange.Copy

'knop realusedrange naar powerpoint presentatie op werkblad
Cells(lastrow + 2, 3).Select

With Selection
ActiveSheet.Buttons.Add(ActiveCell.Left, ActiveCell.Top, 120,60).Select
Selection.OnAction = "PERSONAL.XLSB!macro_powerpoint_slide"
Selection.Characters.Text = "Powerpoint" & vbCrLf & "Presentatie"
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Calibri"
.FontStyle = "Standaard"
.Size = 16
End With
End With

End Sub

Sub macro_powerpoint_slide()
'voor referentie naar powerpoint referentie zetten in tools > verwijzingen naar microsoft powerpont object library

Dim pptApp As PowerPoint.Application
Dim pptPrs As PowerPoint.Presentation
Dim pptSld As PowerPoint.Slide
Dim pptshp As PowerPoint.Shape
Dim FSO As Object
Dim I As Integer

Set pptApp = CreateObject("Powerpoint.application")

'powerpoint zichtbaar maken
pptApp.Visible = True

'ophalen sponsors template en slide maken
Set pptPrs = pptApp.Presentations.Open _("C:\VVVierpolders\automation\Templates\TV-sponsors template." &"potx")
Set pptSld = pptPrs.Slides(1)
Set pptshp = pptSld.Shapes(1)

'plakken excel-tabel in powerpoint slide
pptSld.Shapes.PasteSpecial(ppPasteOLEObject).Select

'centreren excel-tabel op slide
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

End Sub

[John Wilson]

If you copy the range manually and in PowerPoint do a Paste Special. What data types are available manually??

If you have a ref to PowerPoint then ppPasteOLEObject should be OK but you might try 10 instead.

[riniheijnen]

Thank you for responsing to my problem.
I have been thinking when this problem started to come up and I am sure it was since last sunday. Then I realized I have been building in a commandbutto in the marcro so the user is able to adjust one or more cells of the table until he wishes to continue.

So I deleted the code for the commandbutton and my macro worked as before so the problem is made by the commandbutton. This code was:

Cells(lastrow + 2, 3).Select
With Selection
ActiveSheet.Buttons.Add(ActiveCell.Left, ActiveCell.Top, 120, 60).Select
Selection.OnAction = "PERSONAL.XLSB!macro_powerpoint_slide"
Selection.Characters.Text = "Powerpoint" & vbCrLf & "Presentatie"
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Calibri"
.FontStyle = "Standaard"
.Size = 16
End With
End With


Sub macro_zaterdag()

Dim pptApp As PowerPoint.Application
Dim pptPrs As PowerPoint.Presentation
Dim pptSld As PowerPoint.Slide
Dim pptshp As PowerPoint.Shape
Dim FSO As Object
Dim I As Integer

Set pptApp = CreateObject("Powerpoint.application")

'powerpoint zichtbaar maken
pptApp.Visible = True

'ophalen sponsors template en slide maken
Set pptPrs = pptApp.Presentations.Open("C:\VVVierpolders\automation\Templates\TV-sponsors template." & "potx")
Set pptSld = pptPrs.Slides(1)
Set pptshp = pptSld.Shapes(1)

'plakken excel-tabel in powerpoint slide
pptSld.Shapes.PasteSpecial(ppPasteOLEObject).Select

'centreren excel-tabel op slide
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

end sub

I am determined to work with a commandbutton as I described so maybe you can tell me what is wrong with the code for the commandbutton.

Hope to hear from you,

Rini

[John Wilson]

I suspect that when you run the PowerPoint macro the clipboard is being cleared so there is nothing to paste.

Could you incorporate the range.copy into the PowerPoint macro to ensure there is something to paste?

[riniheijnen]

John,

What I did was the following:

I declared my range "realusedrange" public so it can be called from all macro's.

Into the powerpoint macro I inserted a realusedrange.copy.

But when I run the macro I receive a different error:

error 91 - Object variable or With block variable not set.

Probably I dit the wrong thing so I hope you will tell me how it should be done.

Rini

[raji2678]

The index of the shape may be wrong. Loop through all the shapes, and if it is the one you want, only then execute the action. Be sure to activate the slide first.

[aviben]

Consider using an alternative as Ezpaste (www EzPaste net)
Avi