Deleting on files via Excel macro

**koltregaskes** · 08-03-2011, 06:42 AM

As I can save a new file with today's date is there a way to delete or move the old file already in the folder? The file name will be in the same format but the date would be different though in the same format, e.g. 25-07-11. Is there anyway the macro could find this old file and delete it or move it?

I see backups of all reports on our network but generally only keep the latest file on our intranet, so I would need to delete the old file on our intranet and move the old file on our network.

Relating post is here.

**royUK** · 08-03-2011, 07:34 AM

Use the Kill command, read the vba help files

**koltregaskes** · 08-03-2011, 10:52 AM

Thank you. How do wildcards work in the Kill statement?

I'd like to delete all files starting with "Combined Town Report" and end with ".xlsx". The bit in the middle is normally the date which differs each day. How would I do this? Every variation I can think of doesn't appear to work.

**koltregaskes** · 08-03-2011, 11:14 AM

An additional question to above. I've tried deleting all .XLS files in a folder on our intranet with the following code but I get the error sayign file not found. What am I doing wrong?:

Kill "http://Intranet/cornerstone/Cornerstone Reports/Town Reports/*.xls"

**koltregaskes** · 08-03-2011, 11:17 AM

And yes there are .xls files in the folder. :-)

**koltregaskes** · 08-03-2011, 11:33 AM

OK, it seems I cannot save or delete anything on that intranet (http) addresses in an Excel macro. Is this correct?

Basically this code works:

FileCopy "foldername\Combined Town Report 00-00-11.xlsx", "foldername\_OLD\" & "Combined Town Report " & Format(Now, "dd-mm-yy") & ".xlsx"

But this doesn't:

FileCopy "foldername\Combined Town Report 00-00-11.xlsx", "http://Intranet\cornerstone\Cornerstone%20Reports\Town%20Reports\" & "Combined Town Report " & Format(Now, "dd-mm-yy") & ".xlsx"

**koltregaskes** · 08-04-2011, 04:52 AM

Any thoughts on why the Kill command will not work with intranet/internet links? FlieCopy also doesn't appear to work.

The error is bad file but the code is exactly the same apart from being online.

**koltregaskes** · 08-09-2011, 05:45 AM

Anyone know why FileCopy and Kill commands don't appear to work on intranet/Internet addresses?

**royUK** · 08-09-2011, 02:19 PM

I suspect it's to prevent malicious deleteing

**koltregaskes** · 08-17-2011, 08:44 AM

Right, shame that as it means the marcro will need some manual work still. Ta for the reply.

**koltregaskes** · 08-17-2011, 11:33 AM

I've found a solution to this, at least for me. I can change the http addresses to \\intranet\ addresses.

Kill and FileCopy commands seem to work with this type of address. Pretty simple in the end.