asked on

Macro - SetWarnings

My macro looks like this

SetWarnings No
OpenQuery
OutputTo
SetWarnings Yes

The Output is from an Access Query to an XL spreadsheet.
When it runs I get a 'file already exists warning' even though I have SetWarnings to 'No'.

How do I stop this warning?

zuijdhoek

Ange, you just can't avoid this message when Access should overwrite an existing file. In order to avoid this message make sure the Excel-file doesn't exist before the export will happen.
You can easely make a function which deletes the Excel-file (as long as it isn't used at the moment you run the macro).
Copy this function into a new module:

Function KillFile(strFileName As String) As Boolean
Kill strFileName
KillFile = True
End Function

Then, modify your macro by inserting the RunCode command (in the Function Name textbox you should type the location of the Excel-file you would like to delete).

SetWarnings No
OpenQuery
RunCode
OutputTo
SetWarnings Yes

Hope this might help you,

Mark

Ange

ASKER

Mark,
Two things:

1. How does the RunCode command know to run KillFile and not something else? (I'm not a programmer so I need a bit of help with this stuff).

2. It didn't work. In the Function Name of the RunCode command I put the XL file location like this; Q:\COMP\AChalmers\Overseas\Journal.xls
This is copied from the Output command where it works fine.
I got a 'can't find file' message when it ran.

Thanks
Ange

ASKER CERTIFIED SOLUTION

zuijdhoek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdneal

What I do is:

Function KillFile()
ON ERROR RESUME NEXT
KILL "file name"
End Function

I don't have the database up right now, so you can look up "ON ERROR RESUME" for particulars. Using on error resume eliminates the need for other checking...

Ange

ASKER

Mark,
Sorry it took so long to come back on this. The problem has actually gone away (changed user requirements). I never did get to the bottom of it but thanks for your input.

Have some points!!

Ange