Close routine

[Jim Smythe]

Hi...

I have a closing routine that shuts down excel and saves
the file to my desktop. How can I remove the 'this file
already exists, would you like to save changes' box that
comes up...

This what I've got so far...

ub auto_close()
MsgBox "PATCH 2004 WILL NOW SAVE CHANGES CLOSE"
CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFullScreen = False
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayWorkbookTabs = True
MenuBars(xlWorksheet).Menus("Data").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Edit").Enabled = True
MenuBars(xlWorksheet).Menus("Format").Enabled = True
MenuBars(xlWorksheet).Menus("Insert").Enabled = True
MenuBars(xlWorksheet).Menus("Window").Enabled = True
MenuBars(xlWorksheet).Menus("Help").Enabled = True
MenuBars(xlWorksheet).Menus("Tools").Enabled = True
MenuBars(xlWorksheet).Menus("View").Enabled = True
Dim wsh As Object
Dim myPath As String

Set wsh = CreateObject("WScript.Shell")
myPath = wsh.SpecialFolders.Item("Desktop")

ActiveWorkbook.SaveAs FileName:="C:\WINDOWS\DESKTOP"
& "\" & "PATCH04.xls", _
FileFormat:=xlWorkbookNormal

ActiveWorkbook.Close savechanges:=True

Application.Quit
End Sub


Cheers

GC

 

[Bob Phillips]

Jim,

Precede with Application.DisplayAlerts=False

and re-set at the end.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Jim,

Prior to the SaveAs, you can delete the existing file with the Kill
statement. Note that using Kill permanently deletes the file; it doesn't put
it in the Recycle Bin. To put a file in the Recycle Bin, see
http://www.cpearson.com/excel/recycle.htm .

On Error Resume Next
Kill "C:\WINDOWS\DESKTOP\PATCH04.xls"
On Error Goto 0
ActiveWorkbook.SaveAs FileName:="C:\WINDOWS\DESKTOP"
& "\" & "PATCH04.xls", _
FileFormat:=xlWorkbookNormal


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com