Mark,

The two subs below (workbook_Open and Before_Close) may already exist
in your workbook module. If so, you need to copy the code in between
the first and last line of each sub in to your existing routines.
Otherwise, just copy the code as written below.

Sounds like the archiving won't be an issue for you. Post back if the
above is not clear.

This needs to be at the top of all code in the workbook module:

Option Explicit
Public LngNm As String
Public ShrtNm As String


Alan

santaviga wrote:
Hi Alan thanks a lot for the code, but I'm sorry I don't know how to write
this into VBA with regards to copying it into VBA, also the code I have is as
follows - Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Stock as of " & Format(Date,
"dd-mm-yy") & ".xls"
End Sub
I changed this to a daily basis as was creating a lot of files at the
moment, when workbook is active it will only be used approx 4-5 times a week.

With the current code how should I import the new code? I've not quite got
it when it comes to writing in VBA yet still new to it, If you could write
the code for me and e-mail to me again it would be much appreciated if that
ok,

Regards and thanks for all your help.


Mark

" wrote:

Mark:

Here is some code that should work. Place all in the Workbook module.
If you already have a BeforeClose and Open event, just add the code to
your existing procedures. Change "P:\My Documents\Temp\" to your
archive path.


A few thoughts; Be sure all users have access to the archive path or
they will get a file path error when closing the workbook. Also, your

other post dealt with a file name that included a time stamp down to
the second. Not sure how often the current copy of the workbook is
updated and saved but, you could end up with a lot of archived files in

a very short time. frame.


Alan


Option Explicit
Public LngNm As String
Public ShrtNm As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Name = ShrtNm Then
Exit Sub
Else
ShrtNm = "P:\My Documents\Temp\" & ShrtNm
Name LngNm As ShrtNm
End If
End Sub

Private Sub Workbook_Open()
LngNm = ThisWorkbook.FullName
ShrtNm = ThisWorkbook.Name
End Sub
santaviga wrote:
Thanks a lot Alan.


Mark

" wrote:

Mark,

I have some code on my office pc I will post tomorrow.

Alan


santaviga wrote:
Yes thats correct Alan, or move the one that is old in comparison to date and
time as I have the file set up to auto update date and time.


Thanks, your a great help.


Mark

" wrote:

By archiving, you want to MOVE yesterday's file to the archive when the
new file is saved, correct?


santaviga wrote:
Hi Need help. I have an excel document that updates date and time everytime
you save it, what can I do to archive the old file to a different folder.
e.g. Archived Data. So when I save the file it asks to replace, I click yes,
the file is saved in original destination and the old file now is moved to
Archived Data Folder.

Anyone help me

Thanks


Mark