+ Reply to Thread
Results 1 to 3 of 3

copying data from Powerpoint to Excel

  1. #1
    DB
    Guest

    copying data from Powerpoint to Excel

    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

  2. #2
    Gareth
    Guest

    Re: copying data from Powerpoint to Excel

    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



  3. #3
    DB
    Guest

    Re: copying data from Powerpoint to Excel

    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

    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1