sharky12345
Well-known Member
- Joined
- Aug 5, 2010
- Messages
- 3,404
- Office Version
- 2016
- Platform
- Windows
I'm using this to update a workbook but it still leaves an instance of Excel running after closing;
For information, 'CloseChanges' is the following;
Can anyone see where I am going wrong or suggest a more efficient way of doing this?
Code:
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;
Code:
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?