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

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


---
Message posted from http://www.ExcelForum.com/

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


-----Original Message-----
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


---
Message posted from http://www.ExcelForum.com/

.

Hi Susanne,

What is the version of Excel you're using

--
Message posted from http://www.ExcelForum.com

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-...ml?tag=lst-0-8
http://downloads-zdnet.com.com/3000-...l?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

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


-----Original Message-----
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


---
Message posted from http://www.ExcelForum.com/

.

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)

"Susanne" wrote in message
...
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


-----Original Message-----
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


---
Message posted from http://www.ExcelForum.com/

.

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)

"Frank Kabel" wrote in message
...
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-...ml?tag=lst-0-8
http://downloads-zdnet.com.com/3000-...l?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