Get info from Powerpoint 2003

[ordoff73]

I am attempting to create a history log from PowerPoint 2003 to Excel
2003 using VBA.

Currently there are Daily Slides that are breifed to the boss. There
is a tedious process of manually copying the information from the
Powerpoint slides to an Excel Document, each colum for each day, for a
monthly history rollup report. The data in powerpoint is in an Excel
Chart, however when attempting to record a macro starting in excel or
powerpoint as a starting point, VBA will not open or close any
documents that is outside of the program that I am using, I am not an
expert, but I am also not a novice, but I am perplexed.

Essentially the end result would be once the powerpoint slides have
been updated to run a macro that copies only the necessary data, opens
the history rollup excel file, copies the data into the next
corresponding column, dates the top row, and then closes the excel
document.

I know this is probably more complicated than it sounds, however I have
been unsuccessful in even getting it started, because it seems as though
the two programs will not speak to each other.

Thank you in advance for any insight and ideas that you all may offer.

 

[Guest]

This worked for me. Obviously it will take a little work on your part to
match your situation, but it should give you a start.

Create a Reference to Microsoft Powerpoint in your project in Excel. (In
the VBE, Tools=>References, scroll to Microsoft Powerpoint and click the
checkbox). Then paste in this code in a new module.

to test this, create a single slide powerpoint presentation blank except for
a pasted excel range with data in B4:E17 (or). Save it and use that name in
the code.

Option Explicit
Sub AAA()
Dim ppt As PowerPoint.Application
Dim pres As PowerPoint.Presentation
Dim shp As PowerPoint.Shape
Dim oleObj As PowerPoint.OLEFormat
Dim bk As Excel.Workbook
Dim sh As Excel.Worksheet
Dim rng1 As Excel.Range
Dim rng As Excel.Range
Dim sVerb As Variant
Dim nCount As Long

Set ppt = New PowerPoint.Application
Set sh = ActiveSheet
Set rng1 = sh.Cells(1, Columns.Count).End(xlToLeft)(1, 2)
'Debug.Print ppt.Name, ppt.Presentations.Count
ppt.Visible = msoTrue
Set pres = ppt.Presentations.Open( _
Filename:="C:\Data\Excel_PPT.ppt", _
ReadOnly:=msoFalse)
For Each shp In pres.Slides(1).Shapes
If shp.Type = msoEmbeddedOLEObject Or _
shp.Type = msoLinkedOLEObject Then
Set oleObj = shp.OLEFormat
If TypeName(oleObj.Object) = "Workbook" Then
For Each sVerb In oleObj.ObjectVerbs
nCount = nCount + 1
Debug.Print nCount, sVerb
If sVerb = "Open" Then
' oleObj.DoVerb nCount
Set bk = oleObj.Object
Set rng = bk.Worksheets(1).Range("B4:E17")
rng.Copy Destination:=rng1
Set rng = Nothing
Set bk = Nothing
End If
Next
End If
End If
Next
Set oleObj = Nothing
Set shp = Nothing
pres.Close
Set pres = Nothing
ppt.Quit
Set ppt = Nothing
End Sub

 

[Guest]

If the data in your presentation is in an embeded Excel worksheet, then you
can write the code in the sheet's module and bypasss the automation issue all
together. You edit (double-clicking) the embeded worksheet, then execute the
macro.