View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Cush Cush is offline
external usenet poster
 
Posts: 126
Default 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