How do I overwrite files without prompt in Access 2007?

Question

I am trying to output multiple tables to excel files. Every time I run the macro it prompts me to overwrite the old file. I am looking for a method that doesn't involve send keys because it locks the keyboard and mouse until the macro completes.

What would the best solution be?

Answer 1

Have another macro that runs first and deletes the file if it already exists.

Answer 2

Not sure about Access, but most other Office applications have a DisplayAlerts property or something similar, so you can do Application.DisplayAlerts = False before doing operations that normally cause a dialog to appear.

Answer 3

Are you using DoCmd.OutputTo?

 DoCmd.OutputTo acOutputTable, "Table1", acFormatXLS, "c:\temp\test.xls"

This doesn't appear to prompt to overwrite existing files.

Answer 4

You can do something like this, but it's cludgy and not very robust.

DoCmd.SetWarnings False
'do stuff
DoCmd.SetWarnings true

What you could also do is first see if the file is there, and then if it is, delete it (of course this destroys any special file permissions you had set on it).

If Dir(strPath) <> "" Then
    Kill (strPath) 'Delete (strPath)
End If
DoCmd.TransferSpreadsheet acExport, _
                            acSpreadsheetTypeExcel8, _
                            "MyTableQueryName", _
                            strPath, _
                            True

Answer 5

I suggest you get away from using macros as soon as possible. They are not robust (no error handling) and not manageable (how do you figure out where a macro is supposed to be used?).

DoCmd.OutputTo or one of the DoCmd.TransferXXX commands is going to be much eaiser to deal with in the long run.