MS Access - Output Data in Windows folder, can be deleted after procedure?

Question

Not sure if this question is going to make sense or not be here goes:

So I have a some VB that outputs data from some queries. It simply export that resulting dataset from the query out as excel into an excel file, and there are several that do this. These all trip automatically (open and close events on forms) as a part of a little automation trick that I use to create a powerpoint presentation with (or eventually let the users do it) that run a bunch of queries of top Nth, and then create that charts, and then creates the ppt with the charts and data.

blah blah, so what I am wondering is, is there a way in access I can empty that specific folder (delete all those output excel files) at the end of the process. because they continually have to be removed, or the next time its run, you end up with 25 "would you like to replace this file?" messages, and that is just annoying. i am pretty sure there is a macro method of doing this....but i really do not know???

the reason i export them like this is i need the data to match to the chart in the presentation, and the best way (relative) that I can muster, is copying and pasting from excel into the notes section of the ppt. i don't know how to vb it from access into the notes section, i don't know how to vb it from excel into the notes section so this will have to do i suppose.

thanks guys!

Answer 1

You can use

Kill (strPath)

to delete a file from disk, where strPath is the path to the file. Repeat as necessary.

Answer 2

If you don't want to itemize each file you can put something like:

del C:\FolderName\*.xls

In a batch file.

Then in VBA run the batch file:

Call Shell("C:\folderdelete.bat")

OR if you feel more code centric:

Dim sDelFile As String
sDelFile = Dir$("c:\FolderName\*.xls")
Do While sDelFile <> ""
    Kill "c:\FolderName\" & sDelFile
    'need to specify full path again because a file was deleted 1
    MyFile = Dir$("c:\FolderName\*.xls")
Loop

I'd really test this before putting into production.