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!

Solution 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

Solution 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.

Solution 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.

Comments

[Paul B.]

That's the funny thing: I used JMP's code (no additional variables), added Set oPPTPres = Nothing and still PowerPoint would not close.

[Scott Conover]

Paul B, you're right - setting the variable to Nothing deallocates the variable - it does not kill the application. I will modify my answer accordingly.

[JMP]

Thank you JackOrangeLantern. I have used similar batch scripts to kill applications but was unaware I could use them in VBA. And didn't even think about them, frankly. I will try this out and come back to mark this a success if it indeed works for me. Thank you again.

[JMP]

Jack, this works great. Thank you very much. I feel like I have to give Paul the check, though, as he was first. Thank you very much for your help, though. Great to learn that VBA can run shell functions like that. Will definitely help in the future.

[Scott Conover]

@JMP, fair enough to give Paul the check. However, feel to free to consider an upvote for any post that you feel helps, even if you have prescribed solution already in hand. Have fun exploring the shell - just be careful ;)

[JMP]

Yessir--now that my street cred is high enough, I've added the upvote. I'll be sure to get carried away with a BruteForce = "SelfDestruct /F"