Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sub routine | Excel Discussion (Misc queries) | |||
why do all excel worksheets/workbooks close when I close one? | Excel Discussion (Misc queries) | |||
Excel shoud not close all active books when clicking close button | Excel Discussion (Misc queries) | |||
excel - Windows close button (x) should only close active workboo. | Setting up and Configuration of Excel | |||
Need VBA Routine | Excel Programming |