seinc
asked on
Excel process won't end with Application.Quit
I am opening an excel file from vba code in an Access database. After I do what I need to do, the excel process does not end in task manager. How do I kill it?
Dim xlapp As Object
Set xlapp = CreateObject("Excel.Applic ation")
xlapp.DisplayAlerts = False
xlapp.Workbooks.Open "...\my excel file"
ActiveWorkbook.RefreshAll
xlapp.Wait (Now + TimeValue("0:00:20"))
MsgBox Range("A1").Value & " " & Range("B1").Value
ActiveWorkbook.Save
xlapp.Quit
Set xlapp = Nothing
Dim xlapp As Object
Set xlapp = CreateObject("Excel.Applic
xlapp.DisplayAlerts = False
xlapp.Workbooks.Open "...\my excel file"
ActiveWorkbook.RefreshAll
xlapp.Wait (Now + TimeValue("0:00:20"))
MsgBox Range("A1").Value & " " & Range("B1").Value
ActiveWorkbook.Save
xlapp.Quit
Set xlapp = Nothing
Private Sub Command1_Click()
Dim xlapp, xlWkb, xlsWSheet As Object
Set xlapp = CreateObject("Excel.Applic ation")
xlapp.Visible = True
xlapp.DisplayAlerts = False
Set xlWkb = xlapp.Workbooks.Open("c:\B ook1.xls")
Set xlsWSheet = xlWkb.ActiveSheet
xlWkb.RefreshAll
xlapp.Wait (Now + TimeValue("0:00:20"))
MsgBox xlsWSheet.Range("A1").Valu e & " " & xlsWSheet.Range("B1").Valu e
xlWkb.Save
xlWkb.Close
xlapp.Quit
' Cleaning up the variables
Set xlapp = Nothing
Set xlWkb = Nothing
Set xlsWSheet = Nothing
end sub
Dim xlapp, xlWkb, xlsWSheet As Object
Set xlapp = CreateObject("Excel.Applic
xlapp.Visible = True
xlapp.DisplayAlerts = False
Set xlWkb = xlapp.Workbooks.Open("c:\B
Set xlsWSheet = xlWkb.ActiveSheet
xlWkb.RefreshAll
xlapp.Wait (Now + TimeValue("0:00:20"))
MsgBox xlsWSheet.Range("A1").Valu
xlWkb.Save
xlWkb.Close
xlapp.Quit
' Cleaning up the variables
Set xlapp = Nothing
Set xlWkb = Nothing
Set xlsWSheet = Nothing
end sub
ASKER
I tried your suggestion Tim. Excel becomes visible, then goes away after my 20 second delay. No message boxes even if I enable DisplayAlerts. I should also mention that if I click stop in the code editor or close Access, the Excel process ends.
ASKER
Used your code, Dhaest , but no change. Any other ideas?
Was succesfull for me here. Very strange..
Perhaps changing this from place will help
(first the sheet, then the workbook and then the application)
' Cleaning up the variables
Set xlsWSheet = Nothing
Set xlWkb = Nothing
Set xlapp = Nothing
Perhaps changing this from place will help
(first the sheet, then the workbook and then the application)
' Cleaning up the variables
Set xlsWSheet = Nothing
Set xlWkb = Nothing
Set xlapp = Nothing
ASKER
Tim, somehow I missed it but there is a message box asking me to save. Since I had supressed it, document recovery kept saving recovery versions of the file. I guess now I need to know why my xlWkb.save is not working.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Goes for this too:
ActiveWorkbook.Save
should be
xlapp.ActiveWorkbook.Save
Look through your code for any other "global" object references that should be qualified with the application instance.
ActiveWorkbook.Save
should be
xlapp.ActiveWorkbook.Save
Look through your code for any other "global" object references that should be qualified with the application instance.
Probably because despite setting displayalerts = false there is a dialog or something that you cannot see that is preventing it from quitting.
Try adding xlapp.visible = True in your code and running it to see whether this is the case.
Tim Cottee