PDA

View Full Version : VBA Excel data to Powerpoint



MCardol
02-17-2016, 01:41 AM
Hi all,
I'm trying to make a code work in Excel 2013 (see below). I have a saved excel and powerpoint file. I'm trying to paste individual excel data to individual powerpoint slides that already exist, for instance A3 to slide 3, A4 to slide 4, etc. The errors I get are mostly at "oPPSlide.Shapes.Paste.Select": either integer out of range or shape is not active/found. What am I doing wrong here? Does it have something to do with the current slide not being active or the shape not defined (enough)?
Thanks in advance for all replies!


Option Explicit

Sub Test()
Dim oPPApp As Object, oPPPrsn As Object, oPPSlide As Object
Dim oPPShape As Object
Dim FlName As String
Dim i As integer
'~~> The relevant ppt file
FlName = "C:\Test.PPTM"
'~~> Establish an PowerPoint application object
On Error Resume Next
Set oPPApp = GetObject(, "PowerPoint.Application")
If Err.Number <> 0 Then
Set oPPApp = CreateObject("PowerPoint.Application")
End If
Err.Clear
On Error GoTo 0
oPPApp.Visible = True
'~~> Open the relevant ppt file
Set oPPPrsn = oPPApp.Presentations.Open(FlName)
For i=3 to ThisWorkbook.Sheets("RMs").Range("A65000").end(xlup).row
'~~> Change this to the relevant slide which has the shape
Set oPPSlide = oPPPrsn.Slides(i)
'~~> Write to the shape
ThisWorkbook.Sheets("RMs").Range("A" & i).CopyPicture Appearance:=xlScreen, _
Format:=xlPicture oPPSlide.Shapes.Paste.Select
'~~> Rest of the code
End Sub

snb
02-17-2016, 03:52 AM
If you already have a powerpointpresentation you'd better use (instead of this code from RondeBruin)


Sub M_snb()
sn=thisworkbook.sheets("RMs").cells(1).currentregion

with getobject("G:\OF\voorbeeld.pptx")
for j=1 to ubound(sn)
with .slides.add

end with
next
end with
End Sub

But I don't think your cells in Excel contain 'pictures' ?

Maybe you'd better ask in Helpmij.nl

MCardol
02-17-2016, 04:43 AM
But I don't think your cells in Excel contain 'pictures' ?
Maybe you'd better ask in Helpmij.nl

Thanks for the quick reply!
You're right, it's only numerical data from cells. I don't understand the code you're supplying and ends with the error '13: mismatch'.
Putting this on Helpmij.nl will give me a better understanding of the code?

snb
02-17-2016, 04:53 AM
There are some more helpers there. You can also place sample files. Why would you want a separate slide per cell, for example?

If you hadn't had an error message, something would have been thoroughly wrong.

MCardol
02-17-2016, 05:55 AM
Ok, then I'm going to put my question there. The idea is to eventually have an info ppt with 150+ slides of which each slide has a unique number linked to a product.

The perfect solution would be to have a macro to from Excel that places them or updates them all (deletes and puts down new/same value again).

snb
02-21-2016, 05:32 AM
Here’s how it can:


Sub M_snb()
Sn = Sheets(1). Cells(1). CurrentRegion
With GetObject("G:\OF\example.pptx")
For j = 1 To UBound(sn)
. Slides(j). Shapes("TextBox 1"). TextFrame.TextRange.Text = sn(j,1)
Next
.Application.visible=-1
End with
End Sub