Workbook_Beforeclose

[Ed Davis]

I am trying to backup workbooks upon closing.
I run a save all macro and then Application.Quit .
All workbooks are saved no backup is done unless I close the book.
The application does not quit. and all the workbooks are still open.
When I do not use the Workbook_Beforeclose everthing works fine but no
backup.


I have put the following in the This Workbook module for each workbook. The
names and paths are different for each workbook.

THISWORKBOOK MODULE:

ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Backup\Sales\Sales Close " &
Format(Now, "dd-mm") & ".xls"



This is my save all.

Public Sub Save_All()
' This macro will save all of the workbooks open in Excel.
Dim wb As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each wb In Workbooks
wb.Save
Next wb
End Sub

Is there anything I can do to makesure the workbook is backed up the date
string I am using is enough as long as the backup is over written if the
same date.

 

[Gary''s Student]

I would use two loops:

1. in the first loop save ALL workbooks
2. in the second loop, close all workbooks except ThisWorkbook
3. after the second loop perform the Application.Quit

You need 2 & 3 to avoid accidentally closing the workbook running the macro
before the other workbooks get closed.

 

[Ed Davis]

I am doing that now but the backup does not get done.

 

[Bernie Deitrick]

Ed,

In each of your workbooks, put a macro named SaveMe, with the code that you showed, customized for
each workbook.

Then use

Sub SaveMe()
' I like yy-mm-dd to make it easier to sort by name and get date sorting at the same time....
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & _
"\Backup\Sales\Sales Close " & Format(Now, "yy-mm-dd") & ".xls"
End Sub


Then use this to back up all open workbooks:

Sub SaveAll()
Dim WBook As Workbook

On Error Resume Next
Application.DisplayAlerts = False
For Each WBook In Application.Workbooks
Application.Run (WBook.Name & "!SaveMe")
Next WBook

Application.DisplayAlerts = True
End Sub

 

[Ed Davis]

Using this code it never gets to save the file.
In my save_all I put a msgbox telling me it is calling save_all for each
book but the file is never saved so it then asks if I want to save the file
because of the quit statement.

--
Thank You in Advance
Ed Davis

Ed,

In each of your workbooks, put a macro named SaveMe, with the code that
you showed, customized for each workbook.

Then use

Sub SaveMe()
' I like yy-mm-dd to make it easier to sort by name and get date sorting
at the same time....
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & _
"\Backup\Sales\Sales Close " & Format(Now, "yy-mm-dd") & ".xls"
End Sub


Then use this to back up all open workbooks:

Sub SaveAll()
Dim WBook As Workbook

On Error Resume Next
Application.DisplayAlerts = False
For Each WBook In Application.Workbooks
Application.Run (WBook.Name & "!SaveMe")
Next WBook

Application.DisplayAlerts = True
End Sub

 

[Bernie Deitrick]

Ed,

SaveCopyAs does not save the file - it saves a copy. Try adding this to each SaveMe macro:

ThisWorkbook.Save

Like so....


Sub SaveMe()
Msgbox ThisWorkbook.FullName & " will now be saved..."
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & _
"\Backup\Sales\Sales Close " & Format(Now, "yy-mm-dd") & ".xls"
ThisWorkbook.Save
End Sub

HTH,
Bernie
MS Excel MVP

Using this code it never gets to save the file.
In my save_all I put a msgbox telling me it is calling save_all for each book but the file is
never saved so it then asks if I want to save the file because of the quit statement.

 

[Ed Davis]

This is how I am calling the macros.
CODE:
Call Save_All
MsgBox "calling Quit"
Call Quit ' Application.Quit
END OF CODE:

The msbox tells me it is calling the save me for each book.
but nothing happens.
it does not save a copy or the workbook itself.
I do not get the msgbox from the SaveMe sub
It appears that it does not even get there.




Sub SaveMe()
' I like yy-mm-dd to make it easier to sort by name and get date sorting at
the same time....
Application.DisplayAlerts = False
MsgBox "Save ME " & WBook.Name
ThisWorkbook.Save
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Backup\Sales\Sales Close
" & Format(Now, "yy-mm-dd") & ".xls"
Application.DisplayAlerts = True

End Sub


Sub Save_All()
Dim WBook As Workbook
On Error Resume Next

For Each WBook In Application.Workbooks
Application.DisplayAlerts = False
MsgBox "Calling Saveme " & WBook.Name

Application.Run (WBook.Name & "!SaveMe")
Application.DisplayAlerts = True
Next WBook
End Sub

 

[Bernie Deitrick]

Ed,

It works for me in my test workbooks.

Is your message this message?
MsgBox "Calling Saveme " & WBook.Name

or this message?
MsgBox "Save ME " & WBook.Name



If you have the SaveMe code as shown in each workbook, WBook.Name should give you an error each
time, since WBook is used as the looping variable in the master macro, and is not declared in each
workbook.

What do you get if you change

MsgBox "Save ME " & WBook.Name

to

MsgBox "Save ME " & ThisWorkbook.Book.Name

in each workbook's SaveMe macro?

HTH,
Bernie
MS Excel MVP

 

[Ed Davis]

This is the code that is not working.
' Application.Run (WBook.Name & "!SaveMe")

When I change it to Call Saveme it works for the first book only.
If I do not change it it does not work at all.
I have the saveme macro in each workbook the same. The paths and other info
is different for each workbook.


as follows:
MsgBox "Save ME " & ThisWorkbook.Name
ThisWorkbook.Save
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Backup\Sales\Sales Close "
& Format(Now, "yy-mm-dd") & ".xls"


When I change it to Call I see the first message for each book and the the
second one. but with the same workbook name five times.

 

[Bernie Deitrick]

My bad - I did not have any filenames with spaces. If your file names have spaces, you need to use

For Each WBook In Application.Workbooks
Application.Run ("'" & WBook.Name & "'!SaveMe")
Next WBook

HTH,
Bernie
MS Excel MVP

 

[Ed Davis]

That seems to have done it.

Thank you very much.
I just checked and all files that I wanted did get done properly.

 

[Bernie Deitrick]

Great!

Bernie
MS Excel MVP