copying data from Powerpoint to Excel

[Guest]

I have a powerpoint presentation with one slide. There are text ranges and
shapes that I want to gather data from and copy to Excel. I'm not familar
with the powerpoint object model. Has anyone done this type of programming
that can help?

DB

 

[Gareth]

Hi,

I wrote a VB application to harvest data from Powerpoint and dump it
into Word - or was it Excel? I don't remember - suffice to say I lost
all of the code when both my main and back up hard drives were trashed
during shipping. There's a lesson to be learnt there.

Anyway, as always, your best bet is to open up Powerpoint, record some
macros and explore the object model from there.

To get you started, basically you can either use late or early binding
to access Powerpoint. The below code demonstrates the dumping of all of
the textboxes

Sub PPEarlyBinding()
'Make a reference in your XL project to
'MS Powerpoint Object Model
Dim
Dim s As Slide
Dim sh As Shape

For Each s In ActivePresentation.Slides
For Each sh In s.Shapes
on error resume next
If sh.Type = msoAutoShape _
Or sh.Type = msoPlaceholder Then
Debug.Print sh.TextFrame.TextRange.Text
End if
on error goto 0
Next sh
Next s
End Sub

Sub PPLateBinding()
'no reference required
Dim oPP As Object
Dim oS As Object
Dim oSh As Object

'We assume PP is already open and has an active presentation
Set oPP = GetObject(, "PowerPoint.Application")

For Each oS In oPP.ActivePresentation.Slides
For Each oSh In oS.Shapes
on error resume next
If oSh.Type = 14 _
Or oSh.Type = 1 Then
Debug.Print oSh.TextFrame.TextRange.Text
End If
on error goto 0
Next oSh
Next oS

Set oPP = Nothing
End Sub

Word of warning / explanation: what confused me initially was
Placeholders. Read up on these!! Basically a slide has a pre-assigned
number of Placeholders i.e. textboxes. (You can change this but to no
more than 3 I think.). So if you loop through looking for the textboxes,
the original ones from the template will be type msoPlaceholder but any
others added by the user will be type msoAutoShape. It all makes sense
in the end but it's a tad frustrating initially.

Other than this, you may like to try microsoft.public.powerpoint for
specific Powerpoint info.

HTH,
Gareth

 

[Guest]

Gareth,

Thank you for your example. I did post a similar question on the
microsoft.public.powerpoint site. I used early binding, then had to loop
through shapes that had a text frame then exclude the rectangle which did not
have text. Then I assigned a string variable so I could identify the
specific text within each textbox. My Select Cast then extracted the
paragraphs by assigning an indexes to extract the bulleted paragraphs (not
shown). It works quickly but I just don't know the PPT object model enough
to write really efficient code. Below is a snipit prior to all the case code.


Thank you so much for responding and offering your code. I have printed it
and placed in my PPT VBA binder for reference. I have not seen a good book
on writing VBA for PowerPoint but did find a good site:
http://www.rdpslides.com/pptfaq/FAQ00032.htm



With ppPres
For Each ppShape In .Slides(1).Shapes
If ppShape.HasTextFrame And _
Left(ppShape.Name, 9) <> "Rectangle" Then
sText = ppShape.TextFrame.TextRange.Paragraphs.Text
With ThisWorkbook.Sheets("data")
Select Case Left(sText, 9)