Automatically output MS Access query into Excel

[JimCarlson]

I've created a macro in Access 2003 to output a query into Excel. I have scheduled a Windows XP task to run daily to run the macro. I want the current values in the query to overwrite the current spreadsheet and I want this to happen automatically without user receiving a prompt that the dataset already exists and do they want to replace it. Can I delete the file in my macro prior to outputing the query? If so how? Or is there a way I can set up a default in excel that doesn't issue the prompt? I would appreciate any suggestions.

[ajalwaysus]

You could try the Kill function to delete the file before re-creating it.

Ex.

Kill "C:\Temp\Test.xls"

-AJ

[Stewart Ross]

Hi. If you are using Excel as an automation server you can use the DisplayAlerts property of the Excel application object to suppress file prompts:

Dim objExcel as Excel.Application

<Code you use to instantiate an Excel workbook and set up the data here>

objExcel.DisplayAlerts = False
objExcel.ActiveWorkBook.SaveAs Filename := strYourFileName
objExcel.DisplayAlerts = True

Use of Kill FileName as AJ suggested is fine too, though.

-Stewart

[NeoPa]

I would have expected the SaveAs method to include a parameter to force-overwrite, but it doesn't. This seems to me to be a limitation of the interface, so Stewart's method of simply hiding the prompt seems to be necessary.

I normally save to a temporary filename then, when I already know the save is successful, I Kill any existing file and rename my new one over the top of it.