Excel to Powerpoint Slow Execution

[Francis de Brienne]

Hello to all,

My problem is this, I am creating Charts on the fly, sending them to
Powerpoint and doing some little formating and then closing and saving
powerpoint. I loop through this for 60 graphs.

The problem is not that the code is not working but that Excel as
problems creating the Powerpoint.application object. It can take up to
30 sec for excel to create the activex component.

Here is the code :

Dim oPPApp As PowerPoint.Application
Dim PPpres As PowerPoint.Presentation
Dim pSlide As PowerPoint.Slide
Dim sFileName As String
Dim iSlide As Integer ' Slide Index

AccessTime = Now()

On Error GoTo ErrCheck
Set oPPApp = CreateObject("Powerpoint.Application")

ErrCheck:
Set oPPApp = CreateObject("Powerpoint.Application")
Resume Next

MsgBox Format(Now() - AccessTime, "hh:mm:ss.s")

sFileName = (...Some File...)
oPPApp.Visible = msoCTrue
Set PPpres = oPPApp.Presentations.Open(sFileName)

I am looking for a way to make the object creation faster. If anybody
can help me, this would be greatly appreciated.

Thanks Francis

 

[Tushar Mehta]

I don't know what else is going on, but both your instantiation of the
PP application and your error handling are themselves error-prone.

First, you have two back-to-back CreateObject statements. Luckily for
you, PP is a single-instance app. If you used the same code with XL
you would have any number of instances of XL in memory with no way to
terminate them (except through CTRL-ALT-DEL).

Second, you execute a 'Resume Next' statement w/o the existence of an
error. That, in itself is an error! Create a watch expression for
err.number and err.description then step through the code and you will
see where the Resume Next line itself generates an error!

A much safer way to instantiate the PP application (or any other
similar app for that matter) would be:

Sub StartOtherApp()
Dim ppApp As PowerPoint.Application, IStartedPP As Boolean
On Error Resume Next
Set ppApp = GetObject(, "powerpoint.application")
On Error GoTo 0
If ppApp Is Nothing Then
IStartedPP = True
Set ppApp = CreateObject("powerpoint.application")
End If
'Do my stuff
If IStartedPP Then
ppApp.Quit
End If
Set ppApp = Nothing
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Francis de Brienne]

Thank you Tushar Mehta for your explanation. I new there was
something wrong with my instances of Powerpoint. I changed my
powerpoint declaration to your method but could still get Error 429
Cannot create activex component.

I know that the problem comes from the fact that opening and closing
powerpoint in a loop can do this type of problem. To remedy this, I
used the sleep method to give the application time to create the
object and everything works great. It only takes between 0.5 and 1.2
sec per slide, which is for me excellent speed because of all the
formating that I am doing.

So here is the sleep function for those who would need it and the
update to your code.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As
Long)

Dim oPPApp As PowerPoint.Application
Dim PPpres As PowerPoint.Presentation
Dim pSlide As PowerPoint.Slide
Dim IStartedPP As Boolean

'AccessTime = Now()
On Error Resume Next
Set oPPApp = GetObject(, "Powerpoint.Application")
On Error GoTo 0
If oPPApp Is Nothing Then
IStartedPP = True
Sleep (200)
Set oPPApp = CreateObject("Powerpoint.Application")
Sleep (200)
End If
'MsgBox Format(Now() - AccessTime, "hh:mm:ss.s")

Thanks again for your quick reply

 

[Tom Ogilvy]

Why create powerpoint every time. Open it once. Do your work multiple
times. Close it once. (if you need to open file in powerpoint, do that,
but don't keep creating and closing powerpoint).

 

[Howard Kaikow]

1. You are creating the Powerpoint object twice.
2. Create the object outside of the loop.
3. If you are running NAV, disable the NAV Office plug-in, but do NOT
disable NAV's AutoProtect.
4. Use With and End With to remove unnecessary references to objects, e.g.:

With oPPApp
.visible = msoCTrue
Set PPpres = .Presentation.Open(sFileName)
End With
5. Likely, there are other code improvements possible in the full code.

 

[Howard Kaikow]

Create the Powerpoint object only once, outside of the loop.

 

[Tushar Mehta]

As the others who have commented on this, I too am at a loss as to why
you see the need to instantiate PP in a loop. In my original example,
the 'Do stuff comment line would be any amount of code, including a
loop. In fact, in practice I would modularize the code along the lines
of:

sub main
dim ppApp as ...
if initialize(ppApp,IstartedPP) then
doStuff ppApp
end if
shutdown ppApp, IStartedPP
end sub

Function Initialize(byref ppApp as ..., _
byref IStartedPP as boolean) as boolean
'A returned True value => _
all initialization requirements were satisfied
'initialization code from original example here _
with the addition at the end of
initialize=not(ppapp is nothing)
end function
sub shutdown(byref ppApp as...,byVal IStartedPP as boolean)
'shutdown code from original example here
end sub

sub doStuff (byval pPApp as...)
'all processing here including any and all loops
end sub
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions