Close routine
 

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

Close routine
 

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)

"Jim Smythe" wrote in message
...
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

Close routine
 

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



"Jim Smythe" wrote in message
...
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