Supress "save changes" when creating a document via Excel automation.

Question

In a line of business application I'm working on, we have the ability to export to Excel, which we achieve through Excel automation. We have two methods of exporting - one which saves the files and then goes to attach them to an Outlook email (again through automation); the other is effectively a 'preview' which performs the export to Excel, but makes the application visible at the end, rather than saving/emailing.

One of the issues being reported is that when they preview, make no changes, and try to close Excel, it prompts with the standard "do you want to save your changes". Their opinion is that as they haven't made any changes, it shouldn't show this message.

Is there any way of suppressing this message, short of forcing a save to a location that we have to manually clean out "at a later point in time"?

Answer 1

How about objWorkbook.Close false

this parameter "false" is responsible for SaveChanges

Does this solves the problem?

Or if you want to force workbook to save at any time put "true" instead.

Answer 2

Hi Rowland,

The following way worked for me:

Set XLHandle =  CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False
XLHandle.Visible = True

Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")

'This is where your user reviews the book and manually closes it.
'If you want to test out this code set a breakpoint at the next line, manually close Excel workbook then proceed with the execution.
'In your App you would need to implement certain synchronization with user actions instead of setting a breakpoint.   

XLHandle.Quit

Set XLBook = Nothing
Set XLHandle = Nothing 

This way you suppress all the pop-ups. You can access DisplayAlerts property any time, not only after creation of automation object.

Thank you, Albert Gareev

http://automation-beyond.com/

Answer 3

Setting the Workbook::Saved property property to true will stop Excel from prompting to save changes (as long as you don't make any subsequent edits)