Excel stuck in the Task Manger's "Processes"

[dan2bhm via AccessMonster.com]

I'm using the following code to create a PowerPoint Presentation based on a
series of queries. This is what happens. I run an Access query, it copies
the results into an excel spreadsheet. I have a few queries built into the
spreadsheet that refresh to update all of the data into a predefined format.
The spreadsheet is saved, the PPT presentation opens and all links are
updated. Then the spreadsheet closes, thus, ending my VBA. It works
perfectly...almost. You see, there is still an instance of excel running in
the background that I can't figure out to close. Normally I wouldn't care,
but the problem is if I try to run the code again, it get's locked up.
However, if I go into my task manager processes and end the process, it'll
work fine. I'm wondering if it is the excel query built into the sheet
that's causing this to happen. I'd appreciate any assistance in getting this
thing to close.

Private Sub Command25_Click()

Dim oApp As Object

'run and copy query
DoCmd.OpenQuery "State Info Slide", acViewNormal
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy

'open the Excel file
Set oApp = CreateObject("Excel.Application")

oApp.Visible = True
oApp.UserControl = True
oApp.Workbooks.Open Filename:= _
"S:\ARR\ARR-D\Database Products\State Info Slides\Creator Templates\
Data.xls"

'paste the data into the excel sheet and refresh all of the queries andpivot
tables
oApp.Sheets("Data (from dB)").Select
oApp.Columns("A:I").Select
oApp.Selection.ClearContents
oApp.Range("A1").Select
oApp.ActiveSheet.Paste
oApp.ActiveWorkbook.RefreshAll

'insert the formulas into each of the sheets
oApp.Sheets(Array("Alerted", "Mobilized", "Deployed", "Demobing",
"Demobilized")).Select
oApp.Sheets("Alerted").Activate
oApp.Range("I2").Select
oApp.ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-8])=TRUE,"""",RC[-8])"
oApp.Range("J2").Select
oApp.ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-9])=TRUE,"""",VLOOKUP(RC[-1],'Unit Info (MTOE)'!C[-9]
:C[-3],4,0))"
oApp.Range("K2").Select
oApp.ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-10])=TRUE,"""",VLOOKUP(RC[-2],'Unit Info (MTOE)'!C[-
10]:C[-4],7,0))"
oApp.Range("L2").Select
oApp.ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-11])=TRUE,"""",RC[-10])"
oApp.Range("M2").Select
oApp.ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-12])=TRUE,"""",RC[-9])"
oApp.Range("N2").Select
oApp.ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-13])=TRUE,"""",IF(ISBLANK(RC[-9])=TRUE,""Pending"",
RC[-9]))"
oApp.Range("I2:N2").Select
oApp.Selection.AutoFill Destination:=Range("I2:N6"), type:=xlFillDefault
oApp.Range("I2:N6").Select

'save as working copy
oApp.ActiveWorkbook.Save

'open PPT Presentation

Set ppt = CreateObject("powerpoint.application")

ppt.Visible = True
On Error Resume Next
ppt.UserControl = True
ppt.Presentations.Open Filename:= _
"S:\ARR\ARR-D\Database Products\State Info Slides\Creator Templates\"
& [Forms]![Map]![Text11] & "ARNG Template.ppt"
ppt.ActivePresentation.UpdateLinks
ppt.ActivePresentation.Save

oApp.Quit
Set oApp = Nothing

DoCmd.Close acQuery, "State Info Slide", acSaveNo

Exit Sub

End Sub

 

[TC]

At a quick glance, I would say that sEtting oApp's UserControl property
to trye, will cause the the refefenced object to continue running, even
after the reference to it has gone away. That's certainly how it works
with Access, it is surely the same with Excel. You only set UserControl
when you /want/ the object to stay around /after/ the reference has
gone.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[dan2bhm via AccessMonster.com]

I'm not following you?? If I remove the 2nd reference to oApp (setting it to
Nothing), I get the same result. Other than that, I'm still lost???