Delete workbook after 30 days ...

[Susanne]

Hi all,

I asked this question before. I have a template that
creates a number of workbooks each day that analyse the
stockmarket. These become obsolete at the end of the
trading day and relevant data transferred to a monthly
workbook.

As these daily workbooks build up and clog up the works I
need a macro that will automatically delete these after
30 days.

I have the date in cell AD1 and need a macro that will
kill files 30 days old.

Thanks in advance,

Susanne

 

[Bob Phillips]

So, enlighten us, what was wrong with the answers supplied there?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Susanne]

Hi Bob,

I was treated like a useless female and told not to
delete files automatically as I would get myself into
trouble ... like one day I might need a file.

If it is not possible to write a kill macro for 30 day
files as I have described then why not say so ...

Thanks in advance,

Susanne

 

[Frank Kabel]

Hi Susanne
we're all just carefull (nothing to do with male/female). Just lost too
much data myself due to automatic programs
But now to your problem:
1. Though it is possible to write a macro to delete all files older
than 30 day this will require more VBA coding than just copy and paste.
In addition to make this code work, Excel has to run and I would
consider such kind of code dangerous (e.g., loss of data due to code
errors, etc.).
2. This was the reason I suggested some other stand-alone tools with
much more flexibility and easier user-interface (together with backup
functionality, etc.).

So I'd look into some shareware/open source programs which will do this
job. You can schedule these programs to run automatically at a specific
time (lunch break, etc.) and perform their deletion tasks

Regards
Frank

 

[JE McGimpsey]

You were hardly treated, IMO," like a useless female" (whatever that
is). Instead, you were treated as a self-described "VBA illiterate".

If you don't know what you're doing, getting yourself into trouble is
rather easy. Whether you're male or female. And nobody who posts here
really has any idea (nor cares) which you are anyway.

XL/VBA is not the best platform for manipulating files. It can certainly
be done, but file system manipulation is better handled via the
operating sytem. Frank Kabel gave you several links to other
alternatives.

If you really want a VBA solution, you'll need to provide some more
data. For starters, when do you want the purge to occur - every day?
every time XL opens? every time the template opens?

Are the files always saved to the same directory? Do you want to kill
any file over the age limit? or only files of a particular name
configuration?

 

[Bob Phillips]

As I recall, Tolgag gave you this code

Dim fso As New FileSystemObject
Dim fi As File
Dim fo As Folder

Set fo = fso.GetFolder("D:\")

For Each fi In fo.Files
If fi.DateCreated < Date - 30 Then
fi.Delete True
End If

Next

You will need to change the line

Set fo = fso.GetFolder("D:\")

to point at the directory you want to delete files in.

Best place to put this is in the Personal.xls file. To do this, go to menu
Tools>Macro>Record New Macro, and make sure that the 'Store macro in' is
Personal Macro Workbook. Name it anything you want, it's just a dummy, then
stop; the recorder (there will be a toolbar that pops up to stop it with).

Then go into the VBIDE (Alt-F11). On the left, there is an explorer pane,
and in there select the VBAProject (Personal.xls) entry. Make sure it is
expanded (+/- icon on the left), and make sure the Microsoft Excel Objects
is expanded (again a +/- icon), and the select the ThisWorkbook object.
Double-click the ThisWorkbook entry. This will open up the code pane. In
here, enter

Private Sub Workbook_Open()

End Sub

and then copy Tolgag's code in between these 2 lines.

If all goes well, this will run every time you start Excel.

I also have a file that deletes files greater than 30 days in a nominated
folder and its sub-folders, which is a VBScript file, and can be run as a
scheduled XP job. I have a similar job to delete temporary files that I run
on login.

By the way, as one who gave advice not to do it, I apologise if you felt the
that way, but not for what I said. A number of us made similar statements,
but we still gave advice on how to do it. I don't think it is a good idea
personally, but it's up to you as to what you do.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
just one remark:
This code will delete all files in the specified folder older than 30
days not only '*.xls' files. So just be careful what files are in this
folder

Frank

 

[Tom Ogilvy]

Seems someone offered code and instructions for using it for doing this but
you responsed:

I didn't understand a single word you said ... as I said
I am VBA illiterate.

Given that, what kind of help are you looking for.

 

[Susanne]

Hi again Bob,

Thanks for your help Bob ... I think we are getting
somewhere.

I understand what you say regarding creating a new
Personal.xls Workbook and inserting code into
ThisWorkbook to delete all files from Folder in chosen
Directory. I appreciate and understand the need to
carefully isolate the chosen folder for this purpose.

I didn't want to have to create a separate Workbook
(Personal.xls) for the purpose of running this code.

Tolgag's code suggests it can be run from within the
Workbook itself. Am I right here and if so I would prefer
to go this way ... can you advise again how/where I
should write this.

Sub doDelete()


Dim fso As New FileSystemObject
Dim fi As File
Dim fo As Folder


Set fo = fso.GetFolder("D:\")

For Each fi In fo.Files
If fi.DateCreated < Date - 30 Then
fi.Delete True
End If

Next

End Sub

Again, thanks in advance.

Susanne

 

[Bob Phillips]

Susanne,

Which workbook? The beauty of Personal.xls is that it is stored in the
XLStart directory, and gets opened automatically when Excel starts, so any
startup macros are also deleted. If in any other workbook, you need to open
that workbook, and then either have a startup macro there, or run it
explicitly. You also need to consider all the sort of questions that JE
raised.

How about this for a suggestion? I will create you a workbook, which will
prompt for a folder (probably using a folder browse type facility where you
navigate to your start folder), will scan through that folder and all of its
sub-folders and get Excel workbooks older than 30 days. It will list all of
these files on a new worksheet which you will be able to select whether to
delete or not, and when you click a button, it will delete all files
selected. This is much safer than an automated delete IMO, as you decide
when it runs, where it starts, and what it deletes. First time might be a
long list, but should reduce after that.

If this would help, just respond here with your email address, and I will do
it tomorrow and mail it to you. By the way, tell me what version of Excel
you have.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)