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
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
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
DB wrote:
> 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,
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)
"Gareth" wrote:
> 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
>
> DB wrote:
> > 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
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks