Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing without saving and without asking the user to save...or no
I want to close a file without saving it and without asking the user whether
to save or not. In the BeforeClose code below this works when the Backup_Transactions code is remove or bypassed. When I include that procedure, the user is asked if he wants to save the file -- even when I have included ThisWorkbook.Saved = True If I enter code like Applications.DisplayAlerts=False, there is no difference. Application.Quit caused a fatal error-- apparently quit before Excel could properly shut itself down Any suggestions? ''''''''''''''''''''''''''' In ThisWorkBook mod: '''''''''''''''''''''''''''' Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False On Error Resume Next DevMode 'Restores toolbars Backup_Transactions ''DO NOT SAVE THIS WBK ''AND CLOSE WITHOUT ASKING THE USER ThisWorkbook.Saved = True End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''' In a standard module: '''''''''''''''''''''''''''''''''''''''''''''' Sub Backup_Transactions() Dim FilePath As String Dim FileName As String Dim FileExtStr As String Dim wb As Workbook Dim iMsg As Object Dim iConf As Object With Application .ScreenUpdating = False .EnableEvents = False End With ''COPY TRANSACTION DB Sheets("TRANSACTIONS").Range("xDB").Copy ''PASTE TO A NEW SHEET Sheets.Add ActiveSheet.Paste Application.CutCopyMode = False ActiveSheet.Name = "Sheet1" ''MOVE NEW SHEET TO A NEW WORKBOOK ActiveSheet.Move ''SAVE NEW WBK THEN CLOSE IT Set wb = ThisWorkbook FilePath = wb.Path & "\Backup\Transactions\" FileName = "TRANSACTIONS" & Format(Now, "dd-mmm-yy h-mm-ss") FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) ActiveWorkbook.SaveAs FilePath & FileName & FileExtStr ''SAVE A COPY TO DATA FOLDER FilePath = wb.Path & "\Data\Transactions\" FileName = "TRANSACTIONS" FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) ''Disable alert so it will overwrite last copy Application.DisplayAlerts = False ActiveWorkbook.SaveAs FilePath & FileName & FileExtStr ActiveWorkbook.Close Application.DisplayAlerts = True Tidy: With Application .ScreenUpdating = True .EnableEvents = True End With Set wb = Nothing Set iMsg = Nothing Set iConf = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing without saving and without asking the user to save...or no
Add this line...
ThisWorkbook.Close SaveChanges:=False -- HTH... Jim Thomlinson "cush" wrote: I want to close a file without saving it and without asking the user whether to save or not. In the BeforeClose code below this works when the Backup_Transactions code is remove or bypassed. When I include that procedure, the user is asked if he wants to save the file -- even when I have included ThisWorkbook.Saved = True If I enter code like Applications.DisplayAlerts=False, there is no difference. Application.Quit caused a fatal error-- apparently quit before Excel could properly shut itself down Any suggestions? ''''''''''''''''''''''''''' In ThisWorkBook mod: '''''''''''''''''''''''''''' Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False On Error Resume Next DevMode 'Restores toolbars Backup_Transactions ''DO NOT SAVE THIS WBK ''AND CLOSE WITHOUT ASKING THE USER ThisWorkbook.Saved = True End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''' In a standard module: '''''''''''''''''''''''''''''''''''''''''''''' Sub Backup_Transactions() Dim FilePath As String Dim FileName As String Dim FileExtStr As String Dim wb As Workbook Dim iMsg As Object Dim iConf As Object With Application .ScreenUpdating = False .EnableEvents = False End With ''COPY TRANSACTION DB Sheets("TRANSACTIONS").Range("xDB").Copy ''PASTE TO A NEW SHEET Sheets.Add ActiveSheet.Paste Application.CutCopyMode = False ActiveSheet.Name = "Sheet1" ''MOVE NEW SHEET TO A NEW WORKBOOK ActiveSheet.Move ''SAVE NEW WBK THEN CLOSE IT Set wb = ThisWorkbook FilePath = wb.Path & "\Backup\Transactions\" FileName = "TRANSACTIONS" & Format(Now, "dd-mmm-yy h-mm-ss") FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) ActiveWorkbook.SaveAs FilePath & FileName & FileExtStr ''SAVE A COPY TO DATA FOLDER FilePath = wb.Path & "\Data\Transactions\" FileName = "TRANSACTIONS" FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) ''Disable alert so it will overwrite last copy Application.DisplayAlerts = False ActiveWorkbook.SaveAs FilePath & FileName & FileExtStr ActiveWorkbook.Close Application.DisplayAlerts = True Tidy: With Application .ScreenUpdating = True .EnableEvents = True End With Set wb = Nothing Set iMsg = Nothing Set iConf = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing without saving and without asking the user to save...o
Sorry I should have looked more closely at your code. In your before close
you call Backup_Transactions which has the line ActiveWorkbook.Close in it. With events disabled it will close the book without executing your ThisWorkbook.Saved = True... Chnage the ActiveWorkbook.Close to ThisWorkbook.Close SaveChanges:=False (or ActiveWorkbook.Close SaveChanges:=False) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Add this line... ThisWorkbook.Close SaveChanges:=False -- HTH... Jim Thomlinson "cush" wrote: I want to close a file without saving it and without asking the user whether to save or not. In the BeforeClose code below this works when the Backup_Transactions code is remove or bypassed. When I include that procedure, the user is asked if he wants to save the file -- even when I have included ThisWorkbook.Saved = True If I enter code like Applications.DisplayAlerts=False, there is no difference. Application.Quit caused a fatal error-- apparently quit before Excel could properly shut itself down Any suggestions? ''''''''''''''''''''''''''' In ThisWorkBook mod: '''''''''''''''''''''''''''' Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False On Error Resume Next DevMode 'Restores toolbars Backup_Transactions ''DO NOT SAVE THIS WBK ''AND CLOSE WITHOUT ASKING THE USER ThisWorkbook.Saved = True End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''' In a standard module: '''''''''''''''''''''''''''''''''''''''''''''' Sub Backup_Transactions() Dim FilePath As String Dim FileName As String Dim FileExtStr As String Dim wb As Workbook Dim iMsg As Object Dim iConf As Object With Application .ScreenUpdating = False .EnableEvents = False End With ''COPY TRANSACTION DB Sheets("TRANSACTIONS").Range("xDB").Copy ''PASTE TO A NEW SHEET Sheets.Add ActiveSheet.Paste Application.CutCopyMode = False ActiveSheet.Name = "Sheet1" ''MOVE NEW SHEET TO A NEW WORKBOOK ActiveSheet.Move ''SAVE NEW WBK THEN CLOSE IT Set wb = ThisWorkbook FilePath = wb.Path & "\Backup\Transactions\" FileName = "TRANSACTIONS" & Format(Now, "dd-mmm-yy h-mm-ss") FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) ActiveWorkbook.SaveAs FilePath & FileName & FileExtStr ''SAVE A COPY TO DATA FOLDER FilePath = wb.Path & "\Data\Transactions\" FileName = "TRANSACTIONS" FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) ''Disable alert so it will overwrite last copy Application.DisplayAlerts = False ActiveWorkbook.SaveAs FilePath & FileName & FileExtStr ActiveWorkbook.Close Application.DisplayAlerts = True Tidy: With Application .ScreenUpdating = True .EnableEvents = True End With Set wb = Nothing Set iMsg = Nothing Set iConf = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing without saving and without asking the user to save...o
Jim,
thanks for the reply, but we need a closer look still .......... The ActiveWorkbook in the Backup_Transactions sub is just a copy of the Transactions worksheet from the original ThisWorkbook. This ActiveWorkbook was created with the statement ActiveSheet.Move I then save-as a couple of times, then close the ActiveWbk with ActiveWorkbook.Close At that point I am back to my original wbk whick I want to now close without giving the user the option of saving. Hence: ThisWorkbook.Saved = True Unfortunately, I am still then presented with the Want-to-Save? dialog "Jim Thomlinson" wrote: Sorry I should have looked more closely at your code. In your before close you call Backup_Transactions which has the line ActiveWorkbook.Close in it. With events disabled it will close the book without executing your ThisWorkbook.Saved = True... Chnage the ActiveWorkbook.Close to ThisWorkbook.Close SaveChanges:=False (or ActiveWorkbook.Close SaveChanges:=False) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Add this line... ThisWorkbook.Close SaveChanges:=False -- HTH... Jim Thomlinson "cush" wrote: I want to close a file without saving it and without asking the user whether to save or not. In the BeforeClose code below this works when the Backup_Transactions code is remove or bypassed. When I include that procedure, the user is asked if he wants to save the file -- even when I have included ThisWorkbook.Saved = True If I enter code like Applications.DisplayAlerts=False, there is no difference. Application.Quit caused a fatal error-- apparently quit before Excel could properly shut itself down Any suggestions? ''''''''''''''''''''''''''' In ThisWorkBook mod: '''''''''''''''''''''''''''' Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False On Error Resume Next DevMode 'Restores toolbars Backup_Transactions ''DO NOT SAVE THIS WBK ''AND CLOSE WITHOUT ASKING THE USER ThisWorkbook.Saved = True End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' ''''' In a standard module: '''''''''''''''''''''''''''''''''''''''''''''' Sub Backup_Transactions() Dim FilePath As String Dim FileName As String Dim FileExtStr As String Dim wb As Workbook Dim iMsg As Object Dim iConf As Object With Application .ScreenUpdating = False .EnableEvents = False End With ''COPY TRANSACTION DB Sheets("TRANSACTIONS").Range("xDB").Copy ''PASTE TO A NEW SHEET Sheets.Add ActiveSheet.Paste Application.CutCopyMode = False ActiveSheet.Name = "Sheet1" ''MOVE NEW SHEET TO A NEW WORKBOOK ActiveSheet.Move ''SAVE NEW WBK THEN CLOSE IT Set wb = ThisWorkbook FilePath = wb.Path & "\Backup\Transactions\" FileName = "TRANSACTIONS" & Format(Now, "dd-mmm-yy h-mm-ss") FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) ActiveWorkbook.SaveAs FilePath & FileName & FileExtStr ''SAVE A COPY TO DATA FOLDER FilePath = wb.Path & "\Data\Transactions\" FileName = "TRANSACTIONS" FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) ''Disable alert so it will overwrite last copy Application.DisplayAlerts = False ActiveWorkbook.SaveAs FilePath & FileName & FileExtStr ActiveWorkbook.Close Application.DisplayAlerts = True Tidy: With Application .ScreenUpdating = True .EnableEvents = True End With Set wb = Nothing Set iMsg = Nothing Set iConf = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving and Closing | Excel Discussion (Misc queries) | |||
Prompting user to answer a question before saving and closing wkbo | Excel Programming | |||
closing & saving | Excel Worksheet Functions | |||
VBA - closing without saving changes | Excel Programming | |||
prevent user from saving file to a folder but allow my code to save from behind. | Excel Programming |