Delete Excel Spreadsheet from Access Macro

[Guest]

What Access macro command (or SQL syntax) would initiate deleting an Excel
spreadsheet?

Any help is appreciated,
Thank you.

 

[ChrisR]

I love this one...

Kill "N:\Common\Resale Pricing Team\C123 Audits\zeroprice.xls"

This will work with any type of file that you call out in the path, not just
Excel.

Comes in handy when you want to use macro to auto-export and don't want to
sit through saying OK to replace existing file. Put all the kills in one
macro and run before exporting.

c-

 

[ChrisR]

Sorry... Put the code in a module, and call the module in a macro using the
RunCode command.

c-

 

[Guest]

Thank you very very much for the interesting info! Unfortunately, I don't
know how to create a module. I think I can work with part of your direction.

Much appreciated,

 

[Chris]

I will walk you through it...

You go to the modules tab in Access (if Access 2000 or later it will be on
left of screen below where you see Tables, Queries, Forms, etc... If Access
97 I think that it is tabs left to right. Again, will be same type of tab
as Tables, Queries, etc...

Once there you should see a button for New. It will open a blank module
that you should be able to name (doing this from memory, so not 100% sure).
Once you have new module it will be for the most part blank page. That is
where you put in your kill statement.

Paste this...
''========================
Option Compare Database
Option Explicit

Function KillFiles ()
On Error Resume Next
Kill ("K:\Petro\Fuel Daily Report Files\Carrier Policing Report.xls")
Kill ("K:\Petro\Fuel Daily Report Files\Carrier Report Card.rtf")
End Function
''========================

Then change the path and file names to meet your needs. Close/save module.

Now you go into your existing macro and for Action, choose RunCode as the
action. At the bottom of page you have to put in parameters (Function name
in this case). In this box you put in the name (). In this example you
would put KillFiles()

Now save macro. When you run the macro it will kill the files specified.

Typically what I do is have a macro that goes as follows...

Setwarnings Off
RunCode to kill necessary files to be replaced
OutputTo for the queries/tables/reports I want to export (the ones I just
deleted and am "refreshing" now by re-exporting)
SetWarnings On

This is a simple way of removing files and replacing with new refreshed
files. Good for automated exporting of reports for users that can expect
them to be in the same place with same name every time they are re-created.

Hope this helps. If you want any further help or this gets confusing, use
(e-mail address removed) to ask me for further details.

c-


--
Thanks,

Chris Rettke
(H) - 312.492.7988
(C) - 630.292.8949
(e-mail address removed)