powerpoint won't 'Quit' using Excel VBA

[almagg]

when i am done with the last PPT presentation i have:

With oPPTApp
.ActivePresentation.Save
.ActivePresentation.Close
.Quit
End With

Set oPPTApp = Nothing

End Sub [then returns to main procedure]

the ActivePresentation closes but not the PPT window.
(both Office and PPT references are checked off)

any ideas?
thanks

 

[Norie]

How did you open PowerPoint initially?

 

[almagg]

here is a 'test' i am using to.
the two Object files are actually public 'cause there are 3 SubProcedures to qwork with 3 different presentations. after the last i want to 'Quit' the PPT app.
i just read that the public dim could be the problem.
but the below does not work either.

Sub test()

Dim oPPTApp As Object
Dim oPPTFile As Object
Dim sRollup
Dim sFolder

sRollup = ActiveWorkbook.Name
sReportMonth = Format(Now() - Day(Now()), "mmm") & " FY" & Format(Now(), "yy")
sFolder = "C:\Monthly Reports\" & sReportMonth & "\TFP EHS Report\"

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

sFileMask = "TSP EHS Metrics Presentation.potx"
Set oPPTFile = oPPTApp.Presentations.Open(sFolder & sFileMask)

With oPPTApp.ActivePresentation
.SaveAs sFolder & "TSP EHS Metrics Presentation " & sReportMonth & ".pptx"
End With

With oPPTApp
.ActivePresentation.Save
.ActivePresentation.Close
.Quit
End With

Set oPPTApp = Nothing

End Sub

 

[Norie]

Why are have you declared the 2 objects as public?

As far as I can see you aren't really using them across subs.

Or are you actually setting them somewhere else in the code?

 

[almagg]

here is part of the actual procedure, which is much longer.

Public sFolder As String 'This is the path to the individual files
Public sFileMask As String 'This is a unique part of the file name.
Public sFile As String 'The file path and name.
Public sNextReportMonth As String 'Used to create a new folder for the next month report.
Public sReportMonth As String 'The report month which is previous to the 'run' month.
Public sRollup As String 'Name of the target wkbk "Site Safety Metrics Rollup" file
Public imyRow As Integer 'Floating variable used to record a row number
Public oPPTApp As Object
Public oPPTFile As Object
Public oPPTShape As PowerPoint.Shape
Public oPPTSlide As PowerPoint.Slide
Public oTxtRng As TextRange
Public oTmpRng As TextRange


Sub TFP_EHS_Report()

sRollup = ActiveWorkbook.Name 'This is the RollUp target workbook

'There will be a folder for each reporting month which is one month previous to the report 'run' month
'i.e., in August the data is picked up for the July report.
'There will be a folder for each report containing the files related to that report.
'This is the path to the "TFP EHS Report",

sReportMonth = Format(Now() - Day(Now()), "mmm") & " FY" & Format(Now(), "yy")
sFolder = "C:\Monthly Reports\" & sReportMonth & "\TFP EHS Report\"

'These procedures are called

nonserviceMonthlyHours 'Line 63

FlashReport 'Line 126

noservice_Event_Details 'Line 188

FourBlock 'Line 249

SimplexGrinnell 'Line 293

EMEA_AllAccidents 'Line 339

APAC 'Line 407

'All the data has been updated and the workbook is saved.

ActiveWorkbook.Save

'Then the PowerPoint presentations are updated.

TFP_EHS_Presentation

TRS_EHS_Presentation

TSP_EHS_Presentation

'Now the folder for the next month is created.

sNextReportMonth = Format(Now(), "mmm") & " FY" & Format(Now(), "yy")
sNewFolder = "C:\Monthly Reports\" & sNextReportMonth
MkDir sNewFolder

'The folder for the 'TFP EHS Report is created
'The Powerpoint templates will be moved there.
'And the "Site Safety Metrics Rollup" fille will be copied there.

sNewFolder = "C:\Monthly Reports\" & sNextReportMonth & "\TFP EHS Report\"
MkDir sNewFolder

'The PowerPoint templates are moved to the new folder and this will also remove them from the old folder.

Name sFolder & "TFP EHS Metrics Presentation.potx" _
As sNewFolder & "TFP EHS Metrics Presentation.potx"

Name sFolder & "TRS EHS Metrics Presentation.potx" _
As sNewFolder & "TRS EHS Metrics Presentation.potx"

Name sFolder & "TSP EHS Metrics Presentation.potx" _
As sNewFolder & "TSP EHS Metrics Presentation.potx"

'Then the 'Rollup' file is copied to the new folder
'Because of "Run-time Error 70" (Access denied - administration rights) 'SaveAs' is used.

ActiveWorkbook.SaveAs "C:\Monthly Reports\" & sNextReportMonth & "\TFP EHS Report\" & sRollup
MsgBox ("The " & Format(Now() - Day(Now()), "mmm") & " FY" & Format(Now(), "yy") & " Report is complete.")
ActiveWorkbook.Close savechanges:=True
End Sub

'[This is the last presentation and where I was hoping to close the PPT app]

Sub TSP_EHS_Presentation()

'links are updated and text is replaced

With oPPTApp
.ActivePresentation.Save
.ActivePresentation.Close
.Quit
End With

Set oPPTApp = Nothing

End Sub '[returns to above sub]

 

[Norie]

I can't test this right now - I've got some problem with my install of PowerPoint.

I still don't see why you are using public variables.

When you use them you normally set them once and that's it, but it seems like you are setting them in every sub.

Another thing, and this might actually be related to the problem, you are opening a PowerPoint template of some sort.

Are you actually using this code to change an existing template or to create new presentations using the template?

 

[almagg]

when the template opens i immediately save as a regular 'pptx' file.
then update links and text.

i understand what you are saying about 'setting' them once.
yes i was setting them for eacdh presentation sub.
BUT
the 'test' procedure in my initial post does not close the app either.
so...

 

[Norie]

I really don't follow this.

In this code you don't even open a PowerPoint presentation.

Also why are you using ActivePresentation instead of the reference to the presentation you are opening?

 

[almagg]

guess i meant #3 post - sub test()
that should close the app.

 

[Norie]

But why are you using ActivePresentation?

You have a reference to the file you are opening, use that.

What would you do after this line in Excel VBA to save and close the workbook you've just opened?

Set wbOpen = Workbooks.Open("C:\Example.xlxs")

If I change the code so it refers to pptFile instead of ActivePresentation it works fine.

The file is saved and closed then PowerPoint is closed.