Delete workbook after say 30 days ...

[Susanne]

Hi all,

I have a long list of workbooks that are clogging up the
works.

Would it be possible to write a macro that would
automatically delete workbooks after 30 days and make
them non-recoverable from Recycle Bin.

I don't understand any VBA so you would need to write it
out for me. I know how to cut/paste into
Worksheet/book/module etc.

Thanks in advance ...

Susanne

 

[tolgag]

Hi,

I wouldn't recommend an automation of such a process and without
programming/adaptation (only with copy and paste), it won't work. But I
can describe, how it can be done.

First you have to make a Reference to the "Microsoft Scripting Runtime"
in VBA Editor, so that you can have a FileSystemObject.

Then following code does the deletion based on date created

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

 

[Susanne]

Hi Toltag,

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

I can however, copy/paste a script into
Worksheet/Workbook or create a Module.

I don't know if this helps ... I have the date created on
opening the workbook (from a template)in cell AD1.

Would it be possible to write a macro that would delete
the workbook 30 days after the date in cell AD1 which I
could add to my template.

Again, you would need to write the macro and tell me
where it needs to go ... sorry to be a pain!!

Thanks in advance,

Susanne

 

[tolgag]

Hi Susanne,

What is the version of Excel you're using

 

[Frank Kabel]

Hi Susanne
I would recommend not to use MS Excel for this task but a stand-alone
tool (a scheduler) dedicated for this purpose. Reason: MS Excel has to
run (AFAIK) to perform these tasks

E.g. some links to shareware products which may help you (no
recommendation, I've never tested these tools, just a shot search at
www.zdnet.com)
http://downloads-zdnet.com.com/3000-2084-10251848.html
http://downloads-zdnet.com.com/3000-2344-6743551.html?tag=lst-0-8
http://downloads-zdnet.com.com/3000-2094-10120076.html?tag=lst-0-11
(there are many, many more, just search for 'Scheduler')


Additional note: Are you sure you automatically want to delete files
(without any user interaction). I wouldn't do that
Frank

 

[Bob Phillips]

Susanne,

The most important thing he said was don't do this, it is not a good idea.
Sooner or later you will delete a file you didn't want deleted, and then
there is no way back unless you have a very good backup strategy.

--

HTH

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

 

[Bob Phillips]

You could even use a VBScript file, and use the NT or XP built in scheduler
(does 98 have a similar tool)?

--

HTH

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