Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |