saving a backup file

[choubix]

hi,

I have the following code in my VBE to save a workbook:


Sub Backup_WB()
Dim Wk As Workbook
Set Wk = Workbooks("ourwb.xls")
Application.DisplayAlerts = False
Wk.SaveAs Filename:="P:/Product Monitors/Backup/ourwb " & _
Format(Date, "YYYYMMDD") & " " & Format(Time, "HHMMSS") & ".xls"
Wk.SaveAs Filename:="P:/Product Monitors/ourwb.xls"
End Sub



the thing is: the workbook is on sahred drive AND is used simultaneously by different users.
is there a way for me to make sure I have the most up to date workbook saved? (a workbook that takes into account all the inputs from all users during the day)

since a few users may have the workbook open on their computer at the same time: won't it save the workbook as many times as there are open workbooks on my colleagues' computers?

right now the macro saves the workbook at 11:59 pm every day

so my objective is to save the most up to date workbook itself AND a backup copy at given intervals of time.

thanks :)

[Bob Phillips]

One of the hazards of shared workbooks. Excel was not designed for sharing and does it very badly. My only suggestion, and it is far from straight-forward, is to save the data to a database and not have shared workbooks.

[choubix]

i agree with you yet it's not my baby and there something like 8 persons using it...
I already managed to coin the idea to get daily backups + dump some old data to make the file lighter and it was everything but easy to get the approval...