Delete files using a macro

fangfacekitty

Board Regular
Joined
Jul 28, 2010
Messages
63
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
Take a look at using a KILL statement, that will delete the named files from a named folder.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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

Board Regular
Joined
Jul 28, 2010
Messages
63
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
Pleased to read you have a working solution and thanks for letting me know. ;)
 

Forum statistics

Threads
1,081,556
Messages
5,359,552
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top