Delete files using a macro

[fangfacekitty]

I have a summary file that contains several tabs for each supplier, and summarizes that data on the Summary tab. The data needs to be sent each month to each supplier. I have a macro that copies the data for each supplier into a separate workbook, and saves the workbook with the Supplier name. A second macro then e-mails the workbooks to the appropriate suppliers. Both of these are working fine.

My problem is how to automatically delete the separate workbooks after they are e-mailed. I have tried doing this by recording the macro but it does not seem to take.

I am pretty much macro illiterate and every one I've done so far has either been through the Record a Macro feature or copied from somewhere else.

Also, I cannot just copy the pertinent rows into an e-mail because the data also contains conditionally formatted graphs and is linked to several source files. Everything will error out in any type of copy/paste.

 

[Trevor G]

Take a look at using a KILL statement, that will delete the named files from a named folder.

 

[fangfacekitty]

Thanks Trevor, although I don't really know how to use a KILL statement either.

 

[Trevor G]

There are a few ways you can achieve this. Look at these examples.

The following can be used to test for the existence of a file, and then to delete it.

<code>Dim aFile As String
aFile = "c:\file_to_delete.xls"
If Len(Dir$(aFile)) > 0 Then
Kill aFile
End If
</code></pre>

Next same effect but fewer (well, none at all) variable declarations. The FileSystemObject is a really useful tool and well worth getting friendly with. Apart from anything else, for text file writing it's massively faster than the legacy alternative

<code>With New FileSystemObject
If .FileExists(yourFilePath) Then
.DeleteFile yourFilepath
End If
End With
</code></pre>

 

[fangfacekitty]

Thanks so much. The first example works great; I couldn't get the second to work, for some reason it doesn't like my file path. But I have a working solution now, thanks again.

 

[Trevor G]

Pleased to read you have a working solution and thanks for letting me know.