A different 'answer prompt' question

[RickT]

Good morning

I have a need for a VBA macro to save a worksheet. However, EXCEL prompts for a YES/NO answer, the default of which is NO and I must answer YES to save the file. I have tried to use the Application.DisplayAlerts = FALSE statement prior to the save, but to no avail because, I believe, of the default answer. If this were a one-time execution, it would not be a problem - I could do it manually. But, it needs to happen several times within the course of a run which loops though many iterations.

How can I get my VBA macro to answer the prompt and save the file?

Thanks in advance.

Rick

 

[sous2817]

I use:

wb.Close savechanges:=True

without any issues. It may be worthwhile posting your code so people can see what you've tried.

 

[RickT]

Thanks for the reply. But, I do not want to close the worksheet, only save it with all changes since the last time it was saved. Remember, this save will have to happen several times within the same run of the application.

The application is running under EXCEL 97 using scripting language but I use calls to VBA macros for some work. I have planned a total rewrite to VBA. However, that is all just background.

The code that I have used is in a VBA macro.

Application.DisplayAlerts = FALSE
ActiveWorkbook.Save
Application.DisplayAlerts = TRUE

Thank you.

 

[sous2817]

The code you posted works for me (saves w/o putting up a prompt). Is that all the code you have for your save routine? (Welcome to the board by the way)

 

[eblake]

I'm not sure which prompt your getting thats a yes no, I believe if you use something like this you can force it to overwrite whatever is there with the conflictresolution..
Activeworkbook.SaveAs FileName:=activeworkbook.Name, conflictresolution:= xlLocalSessionChanges

I've never had a problem with the workbook.save command after turning off the alerts. Can you post more information, some code and the prompt information that your getting?

Thanks

 

[RickT]

The code that I posted does not fail the application, meaning that it does not present an error message. It continues and allows my application to continue with its iterations, but, it does not save the worksheet.

The entire prompt

The file was created using a later version of Microsoft Excel. If you save this file using Microsoft Excel 97, information created with features in the later version may be lost.
- To retain the information in the original file, click No, and use the Save As command (File menu) to save this file with a different name.
- To continue with the Save, click Yes.
Continue with Save?

Yes No

The default is No. I want to use Yes. I can change my macro to eliminate the turnoff/turnon of the display alerts and answer manually by clicking Yes, the file saves and execution continues. But, that is not what I want because the entire application takes from 1 1/2 to 3 hours to complete and I do not want to sit there waiting. I would like the worksheet to be saved automatically periodically.

I hope this all makes sense.

Thank you.

 

[eblake]

Sounds like your using a file that was created in 2007 using a 2003 version of excel.....
You can use something like this to save it back out to 2007, you might need to find the correct code to save it as though....

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileFormat:=52 '52 is the code for xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True