VBA using Excel to pull data from Powerpoint

[Guest]

Is there documentation on opening a powerpoint presentation and extracting
the bulleted text in each slide to Excel using VBA? I also want to open one
presentation at a time by looping through a directory of several presentation
files?

Any direction would help,

DB

 

[Steve Rindsberg]

Is there documentation on opening a powerpoint presentation and extracting
the bulleted text in each slide to Excel using VBA? I also want to open one
presentation at a time by looping through a directory of several presentation
files?

Any direction would help,

North is my favorite during this hot August weather. ;-)

This should help boil the text out of PPT:

Export Text to a text file (Mac or PC)
http://www.rdpslides.com/pptfaq/FAQ00274.htm

And the links beneath this describe how to batch-process files in a folder

Batch Tricks
http://www.rdpslides.com/pptfaq/index.html#Batch_Tricks

 

[Guest]

Steve,
Thank you for the links. I'm going to work with this code today. I could
not find anything on extracting information that is held in an Excel
speadsheet within PowerPoint. My thought was to copy the spreadsheet to
Excel then pull the data from there. Is there another way?

 

[Steve Rindsberg]

Steve,
Thank you for the links. I'm going to work with this code today. I could
not find anything on extracting information that is held in an Excel
speadsheet within PowerPoint. My thought was to copy the spreadsheet to
Excel then pull the data from there. Is there another way?

You can automate Excel directly from within PPT and get at the worksheet data w/o
having to copy it. Google "automate excel" and similar terms ... that should get
you started.

 

[Guest]

I couldn't find anything there. However, I noticed that if I activate the
object, I can select the sheet I want by coding as I do for Excel. However,
I'm having a problem closing the object so I can continue looping through the
other objects. Here's a section of my code:

For Each ppShape In ppPres.Slides(1).Shapes
If ppShape.Name = "Object 2" Then
ppShape.OLEFormat.Activate
Sheets(1).Activate
ActiveSheet.Unprotect
Set rStatus = Sheets(1).Cells(4, 1).CurrentRegion
ActiveSheet.protect
Sheets(2).Activate
'ppPres.SlideMaster(1).Select --- How would I close the
datasheet (Object 2)?

Thank you so much for helping me.

 

[Steve Rindsberg]

I couldn't find anything there. However, I noticed that if I activate the
object, I can select the sheet I want by coding as I do for Excel. However,
I'm having a problem closing the object so I can continue looping through the
other objects. Here's a section of my code:

For Each ppShape In ppPres.Slides(1).Shapes
If ppShape.Name = "Object 2" Then
ppShape.OLEFormat.Activate
Sheets(1).Activate
ActiveSheet.Unprotect
Set rStatus = Sheets(1).Cells(4, 1).CurrentRegion
ActiveSheet.protect
Sheets(2).Activate
'ppPres.SlideMaster(1).Select --- How would I close the
datasheet (Object 2)?

You have this working from within PPT?
I can't imagine that it'd compile and neither can PPT here. It complains bloody murder
<g>

But

With ActiveWindow.Selection.Unselect

should deactivate the shape

 

[Guest]

Steve,

When working within Excel to control PowerPoint, I was able to get the
information out of an embedded Excel workbook within PowerPoint. Here is an
example of the code. Note, when assigning the array to the values in the
Excel sheet within PPT, you have to use text for the cell addresses. You
will get a run-time error if you use numbers. I want to thank you for
helping me and to Jon Peltier for his help!

Sub TestGetWorksheetData()
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppShape As PowerPoint.Shape
Dim xlWkb As Excel.Workbook
Dim vArray As Variant

Set ppApp = New PowerPoint.Application
Set ppPres = GetObject(ThisWorkbook.Path & "\proposed pbr v1.ppt")

For Each ppShape In ppPres.Slides(1).Shapes
If ppShape.Name = "Object 2" Then
Set xlWkb = ppShape.OLEFormat.Object
vArray = xlWkb.Sheets(2).Range("A2:E2").Value
ThisWorkbook.Sheets(2).Range(Cells(1, 1), Cells(1, 5)) = vArray
End If
Next

Set ppPres = Nothing
Set ppApp = Nothing

End Sub

 

[Steve Rindsberg]

Hi,

Yep, I saw your post to Jon. Thanks for coming back and letting us all know that you got it
working and showing code to explain how.

That's what makes this place thrive.