Manipulating PowerPoint Files from Excel

M

MWE

I am trying to do some VBA stuff to PowerPoint files from Excel.
posted a rather long threa
(http://www.excelforum.com/showthread.php?s=&threadid=208071) a fe
weeks ago with lots of details. The only suggestion received was t
use hyperlinks, but I do not understand how you can execute VB
procedures via a hyperlink. Can anyone help me here?

More basically, while running Excel and using VBA, I want to open
PowerPoint file, do a few things to it, save it and then close it.
would think that this need is pretty common. Any help would be greatl
appreciated.

Thank
 
T

Tom Ogilvy

It sounds like you want to use automation:

http://support.microsoft.com/?id=260410
OFF2000: Microsoft Office 2000 Automation Help File Available


http://support.microsoft.com/?id=167223
Microsoft Office 97 Automation Help File Available on MSL


http://support.microsoft.com/?id=253338
INFO: Office Developer Samples and Tools Available for Download


http://support.microsoft.com/support/OfficeDev/FaqVBOffice.asp
Frequently Asked Questions about Microsoft Office Automation Using Visual
Basic


http://support.microsoft.com/support/OfficeDev/offdevinapps.asp
Programming Office from Within Office


Also of interest perhaps.
http://support.microsoft.com/?id=177760
VBA: How to Run Macros in Other Office Programs


http://support.microsoft.com/?id=210111
ACC2000: Using Microsoft Access as an Automation Server
 
P

Pyball

MWE,

You should be able to program PowerPoint from Excel. To do so you wil
have to have access to PowerPoints Object Library. You can do this i
the VBE under Tools>References... then add the reference Microsof
PowerPoint 9.0 Object Library. Once you have access to the PowerPoin
Object Library you should be able to open a PowerPoint file an
manipulate it from Excel VBA.

Hope this helps
 
M

MWE

Pyball said:
*MWE,

You should be able to program PowerPoint from Excel. To do so yo
will have to have access to PowerPoints Object Library. You can d
this in the VBE under Tools>References... then add the referenc
Microsoft PowerPoint 9.0 Object Library. Once you have access to th
PowerPoint Object Library you should be able to open a PowerPoin
file and manipulate it from Excel VBA.

Hope this helps. *
Click to expand...
Thanks for you reply ...
I know all about the ref libs; I have even created some of my own.
have some experience programming other MS Office Appls from inside
say, Excel. The approach I have used is to create an applicatio
object for the target MS Appl, do the things I want to do and then qui
the created object. This is detailed in my earlier thread mentione
above. This approach seems to work OK for:
Excel creating Word
Word creating Excel
Outlook creating Excel
etc.
It even seems to work for Powerpoint creating Excel (although in tha
case, I get errors if I try to save a modified Exel file). But I ca
not get it to work when Excel creates Powerpoint
 
C

Colleyville Alan

Thanks for you reply ...
I know all about the ref libs; I have even created some of my own. I
have some experience programming other MS Office Appls from inside,
say, Excel.
Click to expand...
The approach I have used is to create an application
object for the target MS Appl, do the things I want to do and then quit
the created object.
Yep

But I can not get it to work when Excel creates Powerpoint.
Click to expand...

I have created PPT from Access and also created Excel from Access then
exported the Excel info to PPT as an image. I do not see any reason why you
should be having problems, but then again you have not been specific about
the problems that you are facing. This is code I used to get PPT up and
running and it works fine.

Set oPPT = Nothing 'Releases the object if it had been already
assigned
Set oPPT = GetObject(, "PowerPoint.Application") 'Grab PowerPoint
application if it's already running

Err.Clear
On Error GoTo Err_Command_Sub_Name_Goes_Here

If oPPT Is Nothing Then 'not already running
Set oPPT = CreateObject("PowerPoint.Application")
End If


Once the object variable oPPT is assigned, refer to that variable. If other
object variables are related to it, you set them to refer to the object
variable and not to PowerPoint itself. For example, I established a
variable for a presentation object as oPres and used this code to assign a
specific presentation to it:

Set oPres = oPPT.Presentations(Path + ResultsArray(iCtr).strFileName)

Hope this helps.
 
M

MWE

Tom said:
It sounds like you want to use automation:

*******
rest of reply clipped
*******
--
Regards,
Tom Ogilvy

Tom: you are correct; I am using automation. In particular, I am
using the GetObject method. I read through most of the links you
suggested. They indicate that I am approaching this correctly but
have not provided the clue to what is actually wrong. I am
encountereing errors that I can not resolve. I have repoduced most
of my original thread below so that you/others can see PRECISELY what
I am doing and may be able to suggest what is wrong ...

**********
original thread
**********

I have an application that could be run from Powerpoint with some
interaction with Excel worksheets or from Excel with some interaction
with Powerpoint presentations. I have tried both approach and each
has problems.

If *Powerpoint is the "client"*, I create an Excel object, open a
workbook, do operations, close and save, and then quit the Excel
object. I am using slightly modified code that runs fine in Outlook.
It runs fine in Powerpoint too unless I try to save the workbook
during the close. If I do that, the appl hangs. Relevant code is
reproduced below:

'
' define Excel types and definitions
'
Dim xLApp As Excel.Application
Dim xLBook As Excel.Workbook
Dim xLSheet As Excel.Worksheet
Dim xLPath As String, xLFileName As String
'
' define location & filename of target spreadsheet
'
xLPath = ActivePresentation.Path
xLFileName = "Setup.xls"
'
' define application object
'
Set xLApp = CreateObject("Excel.Application")
'
' open target spreadsheet
' define target worksheet
'
xLApp.Workbooks.Open FileName:=xLPath + "\" +
xLFileName
Set xLSheet = xLApp.Workbooks
(xLFileName).Worksheets("SSU_CurrentProject")
'
' do stuff with the spreadsheet
'
stuff
stuff
'
' close spreadsheet, quit xL
'
xLApp.Workbooks(xLFileName).Close
SaveChanges:=False
xLApp.Quit


This works fine. However if I alter the Close statement to save the
spreadsheet, i.e., SaveChanges:=True , the application hangs. If I go
to "Task Manager" and end the PPT session, I am then prompted with a
"SAVE AS" window for the spreadsheet. If I try to save the
spreadsheet over itself, I get the standard message asking me if I
really want to do that. Regardless of how I answer, the SAVE AS
window remains active until I cancel it. This inability to save the
spreadsheet using the above code is
unique to PowerPoint, i.e., it seems to work OK when Word or Outlook
are the client.

I would rather use *Excel as the client*. I roughed out equivalent
code, relevant stuff is reproduced below

'
' define PPT types and definitions
'
Dim pptApp As PowerPoint.Application
Dim pptPresentation As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptPath As String, pptFileName As String
'
' define location and filename of target presentation
'
pptPath = ActiveWorkbook.Path
'
' define ppt application object
'
Set pptApp = CreateObject("Powerpoint.Application")
'
' open PPT file ... PPTFileName is a public var defined elsewhere
'
pptApp.Presentations.Open Filename:=pptPath + "\" +PPTFileName


I get an error on the Open statement:

RUNTIME ERROR
PRESENTATIONS (UNKNOWN MEMBER) INVALID REQUEST. THE POWERPOINT FRAME
WINDOW DOES NOT EXIST.

The Open statement is identical to an open statement if Powerpoint
were that master, but in that case the statement would be:

Application.Presentations.Open Filename:= ...

When I look at the values for pptPath and PPTFileName (using
debugger), they are correct.

Any help would be appreciated.
Click to expand...
 
T

Tom Ogilvy

Sub PowerPointOLEAutomation()
Dim PPT As Object
Dim Pres As PowerPoint.Presentation
Dim Slide1 As PowerPoint.Slide
Dim Slide2 As PowerPoint.Slide
Dim Slide3 As PowerPoint.Slide
Dim Slide4 As PowerPoint.Slide
Dim i As Integer

'creates the PowerPoint Object
Set PPT = CreateObject("powerpoint.application")
PPT.Visible = True 'Makes PowerPoint visible

' Set Pres = PPT.Presentations.Add 'Adds a blank presentation
Set Pres = PPT.Presentations.Open( _
FileName:="C:\test_Pres.ppt", ReadOnly:=msoFalse)
'adds a blank slide
Set Slide1 = Pres.Slides.Add(1, ppLayoutBlank)
Set Slide2 = Pres.Slides.Add(2, ppLayoutBlank)
Set Slide3 = Pres.Slides.Add(3, ppLayoutBlank)
Set Slide4 = Pres.Slides.Add(4, ppLayoutBlank)

'you can use universal naming convention (UNC) to point to network share
'adds a graphic image to the slide
'these need to be on a separate line
' Slide1.Background.Fill.PresetShaded ppPresetShadeEarlySunset, 1, 1
' Slide2.Background.Fill.PresetShaded ppPresetShadeDaybreak, 1, 1
' Slide3.Background.Fill.PresetShaded ppPresetShadeMahogany, 1, 1
' Slide4.Background.Fill.PresetShaded ppPresetShadeNightfall, 1, 1

For i = 1 To 4 'starts a FOR loop

With Pres.Slides(i)
.Shapes.AddTextbox msoTextOrientationHorizontal, _
20, 20, 40, 40 'adds a text frame

End With
Next i

'copies a chart
ThisWorkbook.Worksheets(1).DrawingObjects("Chart 1").CopyPicture

PPT.ActiveWindow.View.Paste 'pastes the chart

'runs a Slide Show presentation
Pres.SlideShowSettings.Run

'clears the object variable
Set PPT = Nothing 'This does not close the application

End Sub

Ran fine for me from Excel.

Perhaps trying to use an overloaded Plus sign for concatenation rather than
using the & concatenation operator is causing your problem.
 
C

Colleyville Alan

You did not set the application's visible property to true. Try inserting
it after the CreateObject command as shown below...




pptApp.Visible = True
 
M

MWE

Colleyville said:
YOU DID NOT SET THE APPLICATION'S VISIBLE PROPERTY TO TRUE. TR
INSERTING
IT AFTER THE CREATEOBJECT COMMAND AS SHOWN BELOW..
Click to expand...

Your suggestion solved my problem. It seems so obvious ... However, i
also seems reasonable to me that the default for the visibilit
property should be "true"

My code now does most of what I want. I am still running into
strange problem when I try to process more than 49 ppt files in a row.
I create the ppt object, then sequence through a series of ppt file
(open, operate, save, close) and then quit the ppt object. If th
number of ppt files in the sequence is > 49, I get an error. Initiall
the error was "too many open windows". I fixed that by adding code t
close the active window right after closing the ppt file (which als
keeps the task bar from filling up with ppt icons). Now I get an erro
indicating that the 50th file can not be opened even though all 4
previous ones have been closed.

Any thoughts?

Thank
 
M

MWE

Tom said:
*I gave you some working sample code - ran in xl97, Win 98 SE wit
no
problem.
Click to expand...

Tom: many thanks for all of your help. It turns out that the proble
was rather silly ... I did not set the visibility property to Tru
right after I created the ppt object. I am still having a proble
when I process more than 49 ppt files at a time (see another reply)

You have always been quite helpful and I really appreciate it
 
J

Jon Peltier

Alternatively, you can wait until you're done to set .Visible, if you
open the presentation with the WithWindow argument set to False:

Set pptPreso = ppt.presentations.open(FileName:="C:\TEMP\PPT.ppt", _
withwindow:=False)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Colleyville said:
You did not set the application's visible property to true. Try inserting
it after the CreateObject command as shown below...





pptApp.Visible = True
Click to expand...
 
C

Colleyville Alan

Jon Peltier said:
Alternatively, you can wait until you're done to set .Visible, if you
open the presentation with the WithWindow argument set to False:

Set pptPreso = ppt.presentations.open(FileName:="C:\TEMP\PPT.ppt", _
withwindow:=False)

- Jon
Click to expand...

Cool tip - thanks.
 
C

Colleyville Alan

MWE > said:
Your suggestion solved my problem. It seems so obvious ... However, it
also seems reasonable to me that the default for the visibility
property should be "true"

My code now does most of what I want. I am still running into a
strange problem when I try to process more than 49 ppt files in a row.
I create the ppt object, then sequence through a series of ppt files
(open, operate, save, close) and then quit the ppt object. If the
number of ppt files in the sequence is > 49, I get an error. Initially
the error was "too many open windows". I fixed that by adding code to
close the active window right after closing the ppt file (which also
keeps the task bar from filling up with ppt icons). Now I get an error
indicating that the 50th file can not be opened even though all 49
previous ones have been closed.

Any thoughts?
Click to expand...

Though I solved your problem, I am not a skilled programmer. But I had huge
problems with memory leaks and my program kept crashing until I put code in
to release memory at the end of each loop, just before the process repeated
itself. So I will post what I did and maybe you will find it of value.

In my case, I had an array with company info in it, was opening a PPT File
called "Template.ppt", then copying slides from other PPT files and pasting
them into it, then saving the presentation with the company name and going
on to the next company. I tell you all that so the following code at the
end of my procedure to clean up and release memory makes sense. It works,
but good programmers may find fault with it. Still my program was crashing
and no longer is
............................................................................
lots of code and then this to end the sub...
.............................................................................

' This code sets the Title of the ppt file to the name of the Plan
Set oSlide = oPresTemplate.Slides(1)
Set oShape = oSlide.Shapes.Title
oShape.TextFrame.TextRange.Text = strPlanName
Set oShape = Nothing 'Added Aug 4 to release memory

' The following code gets rid of the forward slash that makes VBA think
I'm trying to create
' a new directory - this occurs in the abbreviation P/S for Profit
Sharing. I need to do
' this prior to trying to save the file since the file name is the plan
name with .PPT appended

While InStr(strPlanName, "/") > 0
strPlanNameLeft = Mid$(strPlanName, 1, InStr(strPlanName, "/") - 1)
strPlanNameRight = Mid$(strPlanName, InStr(strPlanName, "/") + 1)
strPlanName = strPlanNameLeft & "_" & strPlanNameRight
Wend

'Save plan with name of company
oPresTemplate.SaveAs DestinationPath + strPlanName & ".PPT" 'Saves
File with Co Name

' Close saved plan and close the object for it
oPPT.Presentations(strPlanName & ".ppt").Close
Set oPresTemplate = Nothing ' added this to try and eliminate memory
leak

'Next line is code to reopen the template file since it was renamed in
the SaveAs procedure
Set oPresTemplate = oPPT.Presentations.Open(Path + filename9, msoFalse,
withwindow:=False)

End Sub
 
Top