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

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

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

"(E-Mail Removed)" 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
>
>

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
>
> "(E-Mail Removed)" 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
> >
> >

Thanks a lot Alan.


Mark

"(E-Mail Removed)" 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
> >
> > "(E-Mail Removed)" 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
> > >
> > >
>
>

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

Public FrmNm As String
Public ToNm As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ToNm = "P:\My Documents\Temp\" & ToNm
Name FrmNm As ToNm
End Sub


Private Sub Workbook_Open()
FrmNm = ThisWorkbook.FullName
ToNm = ThisWorkbook.Name
End Sub


santaviga wrote:
> Thanks a lot Alan.
>
>
> Mark
>
> "(E-Mail Removed)" 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
> > >
> > > "(E-Mail Removed)" 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
> > > >
> > > >
> >
> >

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
>
> "(E-Mail Removed)" 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
> > >
> > > "(E-Mail Removed)" 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
> > > >
> > > >
> >
> >

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

"(E-Mail Removed)" 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
> >
> > "(E-Mail Removed)" 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
> > > >
> > > > "(E-Mail Removed)" 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
> > > > >
> > > > >
> > >
> > >
>
>

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
>
> "(E-Mail Removed)" 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
> > >
> > > "(E-Mail Removed)" 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
> > > > >
> > > > > "(E-Mail Removed)" 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
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >

Hi Alan,

I'm sorry i'm having problems writing the code into existing code with
reference to character spacing and enter etc. Getting errors compile errors
when closing and asking me to Debug but I don't know how to debug the code.


Thanks a lot for your help.


Mark

"(E-Mail Removed)" wrote:

> 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
> >
> > "(E-Mail Removed)" 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
> > > >
> > > > "(E-Mail Removed)" 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
> > > > > >
> > > > > > "(E-Mail Removed)" 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
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
>