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
> > >
|
Similar Threads
