• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

Access Macro - Need to suppress overwrite messages

I have a Macro in Access 2013, it exports results to excel files, I have setup a template, it overwrites the existing files
I have set warnings set to No.

However, it keeps asking about overwriting the file, is there a way to stop this and do it automatically. I need to do it this way as I have one excel file that has references to all the other excel files (collating the results), which have been exported from Access.
1 Solution
did you set ExcelOBject.DisplayAlerts = false
Eric ShermanAccountant/DeveloperCommented:
The File Name Argument of the TransferSpreadsheet Macro Action says ...

The name of the spreadsheet file to import from, export to, or link to. Include the full path. This is a required argument.

Access creates a new spreadsheet when you export data from Access. If the file name is the same as the name of an existing spreadsheet, Access replaces the existing spreadsheet, unless you're exporting to an Excel version 5.0 or later workbook. In that case, Access copies the exported data to the next available new worksheet in the workbook.

If you are importing from or linking to an Excel version 5.0 or later spreadsheet, you can specify a particular worksheet by using the Range argument.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You can:

a. Delete the file before.

b. SetWarnings to False in the macro, which should suppress the error messages (don't forget to set it to true again).

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

melinhomesAuthor Commented:
Thank you all for your comments. Jim I have tested this method works a treat. Many thanks!
Be ABSOLUTELY certain to turn warnings back on or you could suffer the consequences.  Having warnings off is so dangerous during development that whenever I turn them off in code to suppress warning messages, I turn the hourglass on.  Then after I turn warnings back on, I turn the hourglass off.  This gives me a visual clue that warnings are off and I must turn them back on.  This prevents an accident should you be testing and stop the code before it completes.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"or you could suffer the consequences."
You *will* suffer consequences :-)
I'm not sure why you awarded points to excelismagic.  This was not an Excel question and so an Excel answer, even if it is correct, would be irrelevant.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Pat is correct. There is no reason for a Split. Jim's answer is the only correct answer per se.
excelismagic's is neither correct nor relevant for this question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now