Instance of Excel being left after closing

[sharky12345]

I'm using this to update a workbook but it still leaves an instance of Excel running after closing;

Sub InstallUpdate()

[INDENT]Dim SourceFile As Workbook
Dim FilePath As String
Dim FileName As String

FileToOpen = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls")
FilePath = Left$(FileToOpen, InStrRev(FileToOpen, "\"))
FileName = Mid$(FileToOpen, InStrRev(FileToOpen, "\") + 1)
Application.ScreenUpdating = False
Application.EnableEvents = False
ThisWorkbook.SaveAs FilePath & "Temp.xls"
Set SourceFile = Workbooks.Open(FileName:=FileToOpen)
With SourceFile.Sheets("Summary").Range("B8:D37").Copy
End With
ThisWorkbook.Sheets("Summary").Range("B8:D37").PasteSpecial Paste:=xlValues
SourceFile.Sheets("Database").Activate
With SourceFile.Sheets("Database")
Range("A2:X2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
End With
ThisWorkbook.Sheets("Database").Range("A2").PasteSpecial Paste:=xlValues
SourceFile.Sheets("Letters").Activate
With SourceFile.Sheets("Letters")
Range("A2:X2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
End With
ThisWorkbook.Sheets("Letters").Range("A2").PasteSpecial Paste:=xlValues
SourceFile.Sheets("Database").Activate
SourceFile.Sheets("Letters").Activate
SourceFile.Sheets("Summary").Activate
SourceFile.Sheets("Welcome").Activate
On Error Resume Next
MkDir ThisWorkbook.Path & "\Pre-Update Archive"
On Error GoTo 0
ArchivedName = Replace(FileName, ".xls", "")
SourceFile.SaveAs ThisWorkbook.Path & "\Pre-Update Archive\" & ArchivedName & " (" & SourceFile.BuiltinDocumentProperties("Comments") & ")" & ".xls"
Set FileToOpen = Nothing
SourceFile.Close
Set SourceFile = Nothing
Kill FileToOpen
ThisWorkbook.SaveAs FileToOpen
Kill FilePath & "Temp.xls"
Call MsgBox("Update has been completed", vbInformation, "Update complete")
CloseChanges

[/INDENT]End Sub

For information, 'CloseChanges' is the following;

Sub CloseChanges()
SetProperties

[INDENT]RestoreToolbars
Worksheets("Welcome").Activate
ActiveWindow.DisplayWorkbookTabs = False
ActiveWorkbook.Close SaveChanges:=True
Application.Quit[/INDENT]

End Sub

Can anyone see where I am going wrong or suggest a more efficient way of doing this?

 

[davesweep]

ActiveWorkbook.Close SaveChanges:=True
Application.Quit

This is closing the workbook that contains the code before the application.quit instruction is reached.

 

[sharky12345]

Dave, thank you - I don't understand what you mean....

I thought that the Application.Quit, as the final line to run, would close Excel completely, regardless of what was open?

 

[davesweep]

Application.quit will close excel completely, but in your example, you save and close the workbook with the line

ActiveWorkbook.Close SaveChanges:=True

As the subroutine running is stored in the workbook you have just closed, excel never gets to the Application.quit line to be able to execute it, kind of like tearing the last page out of a book......

 

[davesweep]

How about something like

Sub CloseChanges()

SetProperties
RestoreToolbars

Worksheets("Welcome").Activate
ActiveWindow.DisplayWorkbookTabs = False

With Application
.displayalerts = false
ActiveWorkbook.save
.displayalerts = true
.quit 

end with

End Sub

 

[sharky12345]

Chaps - got it, many thanks indeed.