Solved

MS Excel SaveAs Without Prompt, VBA MS Access

Posted on 2011-09-07
2
1,177 Views
Last Modified: 2012-05-12
Hello,

I have the following code at the end of a function that edits an Excel spreadsheet within an Access code, VBA:

apExcel.DisplayAlerts = False
    wkBook.SaveAs FileName:=sFileName
apExcel.DisplayAlerts = True

Supposedly, this should save the file without any prompts.  Strangely, it seems to save the file, the prompt again if I want to save the prompt.  I recently upgraded from Access and Excel 2000 to 2010, and this code worked fine with those.  However, everything I read says this should work.

Anyone have any ideas?

Thanks,

0
Comment
Question by:BMIT
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 36499766
Does the Excel file have a Workbook_BeforeSave macro? If so, that will be triggered by your SaveAs unless you turn events off:
apExcel.EnableEvents = False
apExcel.DisplayAlerts = False
    wkBook.SaveAs FileName:=sFileName
apExcel.DisplayAlerts = True
apExcel.EnableEvents = True

Another possibility is that you try to close the workbook and encounter the Save prompt from that action. You can avoid it with the SaveChanges parameter:
wkBook.Close SaveChanges:=False
0
 

Author Closing Comment

by:BMIT
ID: 36502583
"Another possibility is that you try to close the workbook and encounter the Save prompt from that action. You can avoid it with the SaveChanges parameter:
wkBook.Close SaveChanges:=False"

You hit the nail on the head.  This fixed the issue.

Thanks.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now