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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Take a look at using a KILL statement, that will delete the named files from a named folder.
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
Pleased to read you have a working solution and thanks for letting me know. ;)
 
Upvote 0

Forum statistics

Threads
1,224,455
Messages
6,178,768
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top