I have a spreadsheet which is used daily by the night shift. I want to save a
copy of the workbook to a certain folder with todays date appended to the end
of the filename when they have finished their shift (06:00'ish). In addition,
if the file already exists I want it to automatically overwrite the file but
only if the time is before 21:00 (so that if they use it and close it at the
start of the following shift I dont lose the previous days data). I need this
to be independant of user involvement.

I think I need an event handler procedure but its a bit beyond me!

Thanks in advance....

I think you need to weight your desires against your needs. I would save
the file with the Date and time appended, then you can easily delete copies
you don't need. Better to have too much than not enough. this will
certainly simplify the code need to perform the action and minimize errors.

go to the vbe and look in the project explorer. In the thisworkbook entry
for your workbook, right click on it and select view code. In the resulting
module, in the dropdown boxes at the top of the module select Workbook from
the left and BeforeClose from the right. This will place this declaration
in the module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

You can put your code here

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sName as String
sName = ThisWorkbook.Name
sName = Left(sName,len(sname)-4) & _
Format(now,"yyyymmdd_hh:mm:ss") & ".xls"
Thisworkbook.SavecopyAs sName
End Sub

See Chip Pearson's page on events for an overview:
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"PPM at Brackmills" wrote:

I have a spreadsheet which is used daily by the night shift. I want to save a
copy of the workbook to a certain folder with todays date appended to the end
of the filename when they have finished their shift (06:00'ish). In addition,
if the file already exists I want it to automatically overwrite the file but
only if the time is before 21:00 (so that if they use it and close it at the
start of the following shift I dont lose the previous days data). I need this
to be independant of user involvement.

I think I need an event handler procedure but its a bit beyond me!

Thanks in advance....

That is really cool Tom......I can use that other places in my own stuff. I
learn so much, just by reading your responses......thanks much for being
there.

Vaya con Dios,
Chuck, CABGx3


"Tom Ogilvy" wrote in message
...
I think you need to weight your desires against your needs. I would save
the file with the Date and time appended, then you can easily delete
copies
you don't need. Better to have too much than not enough. this will
certainly simplify the code need to perform the action and minimize
errors.

go to the vbe and look in the project explorer. In the thisworkbook entry
for your workbook, right click on it and select view code. In the
resulting
module, in the dropdown boxes at the top of the module select Workbook
from
the left and BeforeClose from the right. This will place this
declaration
in the module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

You can put your code here

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sName as String
sName = ThisWorkbook.Name
sName = Left(sName,len(sname)-4) & _
Format(now,"yyyymmdd_hh:mm:ss") & ".xls"
Thisworkbook.SavecopyAs sName
End Sub

See Chip Pearson's page on events for an overview:
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"PPM at Brackmills" wrote:

I have a spreadsheet which is used daily by the night shift. I want to
save a
copy of the workbook to a certain folder with todays date appended to
the end
of the filename when they have finished their shift (06:00'ish). In
addition,
if the file already exists I want it to automatically overwrite the file
but
only if the time is before 21:00 (so that if they use it and close it at
the
start of the following shift I dont lose the previous days data). I need
this
to be independant of user involvement.

I think I need an event handler procedure but its a bit beyond me!

Thanks in advance....

Thanks Tom, appreciate the quick response (and at 06:28 AM!) Guess I was
asking a bit much!

"Tom Ogilvy" wrote:

I think you need to weight your desires against your needs. I would save
the file with the Date and time appended, then you can easily delete copies
you don't need. Better to have too much than not enough. this will
certainly simplify the code need to perform the action and minimize errors.

go to the vbe and look in the project explorer. In the thisworkbook entry
for your workbook, right click on it and select view code. In the resulting
module, in the dropdown boxes at the top of the module select Workbook from
the left and BeforeClose from the right. This will place this declaration
in the module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

You can put your code here

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sName as String
sName = ThisWorkbook.Name
sName = Left(sName,len(sname)-4) & _
Format(now,"yyyymmdd_hh:mm:ss") & ".xls"
Thisworkbook.SavecopyAs sName
End Sub

See Chip Pearson's page on events for an overview:
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"PPM at Brackmills" wrote:

I have a spreadsheet which is used daily by the night shift. I want to save a
copy of the workbook to a certain folder with todays date appended to the end
of the filename when they have finished their shift (06:00'ish). In addition,
if the file already exists I want it to automatically overwrite the file but
only if the time is before 21:00 (so that if they use it and close it at the
start of the following shift I dont lose the previous days data). I need this
to be independant of user involvement.

I think I need an event handler procedure but its a bit beyond me!

Thanks in advance....