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

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

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

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