Auto Archive old file to a folder

[Guest]

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

 

[acampbell012]

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

 

[Guest]

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

 

[acampbell012]

Mark,

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

Alan

 

[Guest]

Thanks a lot Alan.


Mark

Mark,

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

Alan

 

[acampbell012]

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

 

[acampbell012]

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

 

[Guest]

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

 

[acampbell012]

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

 

[Guest]

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

 

[acampbell012]

Mark,

I will email a workbook with the code.

Alan

 

[Guest]

Thanks Alan,


Much Appreciated.

Mark

Mark,

I will email a workbook with the code.

Alan

 

[Guest]

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

 

[acampbell012]

(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

 

[Guest]

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

 

[acampbell012]

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

 

[Guest]

Hi Alan,

Fixed it, Mypath was not a valid command.

Thank a lot.


Mark