Auto Archive old file to a folder
 

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

Auto Archive old file to a folder
 

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

Auto Archive old file to a folder
 

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

Auto Archive old file to a folder
 

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

Auto Archive old file to a folder
 

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

Auto Archive old file to a folder
 

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

" 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

Auto Archive old file to a folder
 

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

Auto Archive old file to a folder
 

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

Auto Archive old file to a folder
 

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

Auto Archive old file to a folder
 

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

" 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

" 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

Auto Archive old file to a folder
 

Mark,

I will email a workbook with the code.

Alan

santaviga wrote:
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

" 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

" 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

Auto Archive old file to a folder
 

Thanks Alan,


Much Appreciated.

Mark

" wrote:

Mark,

I will email a workbook with the code.

Alan

santaviga wrote:
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

" 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

" 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

Auto Archive old file to a folder
 

Hi Alan,

Thanks for the e-mail with the code but I cant get it to do what I want,
seems a bit complicated so i'm keeping the code as it is, cant figure it out,
what I would like to change is the following code to save the files in a
specified folder rather than defaulting to My Documents.

Code at the moment is: (Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Stores (Domestic) as of " &
Format(Date, "dd-mm-yy") & ".xls"
End Sub
How would I input into this code that I wish the file to be saved in
"XXXXXXX_XXXXXXX in My Documents.


Thanks

Mark

" wrote:

Mark,

I will email a workbook with the code.

Alan

santaviga wrote:
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

" 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

" 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

Auto Archive old file to a folder
 

(Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim MyPath as String
MyPath = "C:\Dir1\Dir2\" '<<<<<Change this to the path you want to
use. Keep the quotes.
ActiveWorkbook.SaveAs Filename:= MyPath & "Galashiels Stores (Domestic)
as of " & _
Format(Date, "dd-mm-yy") & ".xls"
End Sub

santaviga wrote:
Hi Alan,

Thanks for the e-mail with the code but I cant get it to do what I want,
seems a bit complicated so i'm keeping the code as it is, cant figure it out,
what I would like to change is the following code to save the files in a
specified folder rather than defaulting to My Documents.

Code at the moment is: (Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Stores (Domestic) as of " &
Format(Date, "dd-mm-yy") & ".xls"
End Sub
How would I input into this code that I wish the file to be saved in
"XXXXXXX_XXXXXXX in My Documents.


Thanks

Mark

" wrote:

Mark,

I will email a workbook with the code.

Alan

santaviga wrote:
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

" 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

" 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

Auto Archive old file to a folder
 

Hi Alan,

the following code: ActiveWorkbook.SaveAs Filename:= MyPath & "Galashiels
Stores (Domestic) as of " & Format(Date, "dd-mm-yy") & ".xls"

Is highlightened in yellow asking for debug, any ideas

Mark

" wrote:

(Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim MyPath as String
MyPath = "C:\Dir1\Dir2\" '<<<<<Change this to the path you want to
use. Keep the quotes.
ActiveWorkbook.SaveAs Filename:= MyPath & "Galashiels Stores (Domestic)
as of " & _
Format(Date, "dd-mm-yy") & ".xls"
End Sub

santaviga wrote:
Hi Alan,

Thanks for the e-mail with the code but I cant get it to do what I want,
seems a bit complicated so i'm keeping the code as it is, cant figure it out,
what I would like to change is the following code to save the files in a
specified folder rather than defaulting to My Documents.

Code at the moment is: (Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Stores (Domestic) as of " &
Format(Date, "dd-mm-yy") & ".xls"
End Sub
How would I input into this code that I wish the file to be saved in
"XXXXXXX_XXXXXXX in My Documents.


Thanks

Mark

" wrote:

Mark,

I will email a workbook with the code.

Alan

santaviga wrote:
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

" 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

" 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

Auto Archive old file to a folder
 

Did you change the path of "MyPath"? When I change it to a valid
directory on my machine, the routine runs fine.

santaviga wrote:
Hi Alan,

the following code: ActiveWorkbook.SaveAs Filename:= MyPath & "Galashiels
Stores (Domestic) as of " & Format(Date, "dd-mm-yy") & ".xls"

Is highlightened in yellow asking for debug, any ideas

Mark

" wrote:

(Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim MyPath as String
MyPath = "C:\Dir1\Dir2\" '<<<<<Change this to the path you want to
use. Keep the quotes.
ActiveWorkbook.SaveAs Filename:= MyPath & "Galashiels Stores (Domestic)
as of " & _
Format(Date, "dd-mm-yy") & ".xls"
End Sub

santaviga wrote:
Hi Alan,

Thanks for the e-mail with the code but I cant get it to do what I want,
seems a bit complicated so i'm keeping the code as it is, cant figure it out,
what I would like to change is the following code to save the files in a
specified folder rather than defaulting to My Documents.

Code at the moment is: (Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Stores (Domestic) as of " &
Format(Date, "dd-mm-yy") & ".xls"
End Sub
How would I input into this code that I wish the file to be saved in
"XXXXXXX_XXXXXXX in My Documents.


Thanks

Mark

" wrote:

Mark,

I will email a workbook with the code.

Alan

santaviga wrote:
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

" 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

" 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

Auto Archive old file to a folder
 

Hi Alan,

Fixed it, Mypath was not a valid command.

Thank a lot.


Mark

" wrote:

Did you change the path of "MyPath"? When I change it to a valid
directory on my machine, the routine runs fine.

santaviga wrote:
Hi Alan,

the following code: ActiveWorkbook.SaveAs Filename:= MyPath & "Galashiels
Stores (Domestic) as of " & Format(Date, "dd-mm-yy") & ".xls"

Is highlightened in yellow asking for debug, any ideas

Mark

" wrote:

(Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim MyPath as String
MyPath = "C:\Dir1\Dir2\" '<<<<<Change this to the path you want to
use. Keep the quotes.
ActiveWorkbook.SaveAs Filename:= MyPath & "Galashiels Stores (Domestic)
as of " & _
Format(Date, "dd-mm-yy") & ".xls"
End Sub

santaviga wrote:
Hi Alan,

Thanks for the e-mail with the code but I cant get it to do what I want,
seems a bit complicated so i'm keeping the code as it is, cant figure it out,
what I would like to change is the following code to save the files in a
specified folder rather than defaulting to My Documents.

Code at the moment is: (Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Stores (Domestic) as of " &
Format(Date, "dd-mm-yy") & ".xls"
End Sub
How would I input into this code that I wish the file to be saved in
"XXXXXXX_XXXXXXX in My Documents.


Thanks

Mark

" wrote:

Mark,

I will email a workbook with the code.

Alan

santaviga wrote:
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

" 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

" 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