Close Powerpoint Object with Excel VBA (NOT using Powerpoint.Application)

Question

Hoping someone can help me with some VBA code. I use a VBA loop to paste Excel charts, text boxes, and tables into a Powerpoint template. However, because I cannot be sure that the user will have the Powerpoint Object Library installed, I cannot use the Dim PPTApp as Powerpoint.Application type syntax.

I use objects. It works great. Except for one piece: closing Powerpoint.

Code:

Dim oPPTPres As Object  ' Late binding: This is a PowerPoint.Presentation but we cannot assume that the Microsoft PowerPoint 11 library will be loaded in the workbook that this module has been copied to.
Dim oPPTShape As Object ' Late binding: This is a PowerPoint.Shapebut we cannot assume that the Microsoft PowerPoint 11 library will be loaded in the workbook that this module has been copied to.


PPTFile = Range("PPTFile").value ' Read PowerPoint template file name
Set oPPTPres = GetObject(PPTFile): oPPTPres.Application.Visible = msoTrue ' Switch to or open template file

. . . .

strNewPresPath = Range("OutputFileName").value
oPPTPres.SaveAs strNewPresPath
' Range("PPTFile").value = strNewPresPath
ScreenUpdating = True
oPPTPres.Close 'Closes presentation but not Powerpoint
oPPTPres.Application.Quit 'No noticeable effect

The active presentation will close, but Powerpoint itself stays open (with no file window open). Then, because it is open, when the next one runs (I have a loop that will loop through and do many of these builds back-to-back), it opens up the template as well as the latest built Powerpoint file, creating system locking issues.

Any ideas?

Thank you very much for your help!

Answer 1

I am not entirely sure why your code does not work. I tried to set oPPTPres = Nothing as suggested which did not work either. However, the following way PowerPoint closes on my computer

Dim oPPTPres As Object  ' Late binding: This is a PowerPoint.Presentation but we cannot assume that the Microsoft PowerPoint 11 library will be loaded in the workbook that this module has been copied to.
Dim oPPTShape As Object ' Late binding: This is a PowerPoint.Shapebut we cannot assume that the Microsoft PowerPoint 11 library will be loaded in the workbook that this module has been copied to.
Dim oPPTApp As Object

Set oPPTApp = CreateObject("PowerPoint.Application")
oPPTApp.Visible = True

Set oPPTPres = oPPTApp.Presentations.Open(PPTFile)

...

oPPTPres.Close
Set oPPTPres = Nothing
oPPTApp.Quit
Set oPPTApp = Nothing

Answer 2

JMP,

Sean is correct in terms of removing object from memory, but you need to make sure to release any and all direct references to your powerpoint object as well, in case you store the pointer to your powerpoint in other variables. Notably, however, this will not kill the application and stop the thread - it will simply deallocate your application variables.

Paul B's method of shutting down powerpoint should work fine, and this SO Article has a soft method and a brute-force method of shutting down applications if they remain in memory.

I adapted and tested this simple bruteforce method on relatively permissions-limited settings on my machine from Excel, and it killed the Powerpoint application immediately:

Sub ForcePowerpointExit()


Dim BruteForce As String
BruteForce = "TASKKILL /F /IM powerpnt.exe"

Shell BruteForce, vbHide

End Sub

So that provides you with another option for killing the application.

Answer 3

I believe all the other posters are at least partially correct. Paul B.'s answer should work in most cases.

The only caveat will be if you have yor powerpoint VBA code being called directly from a the user form or an object that is directly referenced by the user form.

In that case there is still a object reference waiting to be removed from memory.

Move all your VBA powerpoint code to a module and hide the userform prior to kicking off the automation (powerpoint) code.

Answer 4

Set oPPTPres = Nothing should remove the reference Excel has to the object, and (hopefully) release it from memory