By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Automatically output MS Access query into Excel

P: 3
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.
Feb 4 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 266
You could try the Kill function to delete the file before re-creating it.

Expand|Select|Wrap|Line Numbers
  1. Kill "C:\Temp\Test.xls"
Feb 4 '10 #2

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Dim objExcel as Excel.Application
  3. <Code you use to instantiate an Excel workbook and set up the data here>
  5. objExcel.DisplayAlerts = False
  6. objExcel.ActiveWorkBook.SaveAs Filename := strYourFileName
  7. objExcel.DisplayAlerts = True
Use of Kill FileName as AJ suggested is fine too, though.

Feb 5 '10 #3

Expert Mod 15k+
P: 31,494
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.
Feb 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.