[SOLVED] Close Excel file without prompting user to save

I am a self-taught user of VB for Excel and I've come along pretty well, but
I'm getting stuck on something that seems like it should be fairly simple to
do. I have two files: File1 contains a macro that performs various
functions on File2. When the File1 macro is finished, I want File1 to close
automatically WITHOUT prompting the user if they wish to save (this file
should never be saved by the user). What can I do to force Excel to close
the file without an option to save?

You can use
ThisWorkbook.Close SaveChanges:=False
or
Workbooks("File1").Close SaveChanges:=False

"JDay01" <[email protected]> wrote in message
news:[email protected]...
> I am a self-taught user of VB for Excel and I've come along pretty well,
but
> I'm getting stuck on something that seems like it should be fairly simple
to
> do. I have two files: File1 contains a macro that performs various
> functions on File2. When the File1 macro is finished, I want File1 to
close
> automatically WITHOUT prompting the user if they wish to save (this file
> should never be saved by the user). What can I do to force Excel to
close
> the file without an option to save?

Here is an excerpt for the VBA help file...

This example closes the workbook that contains the example code and discards
any changes to the workbook by setting the Saved property to True.

ThisWorkbook.Saved = True
ThisWorkbook.Close

You can look up the saved property if you need more explanation...

HTH
"JDay01" wrote:

> I am a self-taught user of VB for Excel and I've come along pretty well, but
> I'm getting stuck on something that seems like it should be fairly simple to
> do. I have two files: File1 contains a macro that performs various
> functions on File2. When the File1 macro is finished, I want File1 to close
> automatically WITHOUT prompting the user if they wish to save (this file
> should never be saved by the user). What can I do to force Excel to close
> the file without an option to save?

Set Application.DisplayAlerts to False, to not show the save changes
dialog box. However, that will stop Excel from prompting it, but still
give the user the ability to save it. You might want to look at the
BeforeSave event, to try to stop them from saving.




*** Sent via Developersdex http://www.developersdex.com ***

Just a heads up for consideration:
I didn't test this particular case, but help says displayalerts = false
pursues the default action of the dialog.
Using displayalerts would suppress the prompt, but the default action would
be taken and that is to save the file.

--
Regards,
Tom Ogilvy



"Darrin Henshaw" <[email protected]> wrote in message
news:%[email protected]...
> Set Application.DisplayAlerts to False, to not show the save changes
> dialog box. However, that will stop Excel from prompting it, but still
> give the user the ability to save it. You might want to look at the
> BeforeSave event, to try to stop them from saving.
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi Tom:

I'm probably wrong <g>, but I seem to remember that this is the one case in
which Excel does not perform the default action (despite what Help says).

Regards,

Vasant

"Tom Ogilvy" <[email protected]> wrote in message
news:u3C%[email protected]...
> Just a heads up for consideration:
> I didn't test this particular case, but help says displayalerts = false
> pursues the default action of the dialog.
> Using displayalerts would suppress the prompt, but the default action
would
> be taken and that is to save the file.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Darrin Henshaw" <[email protected]> wrote in message
> news:%[email protected]...
> > Set Application.DisplayAlerts to False, to not show the save changes
> > dialog box. However, that will stop Excel from prompting it, but still
> > give the user the ability to save it. You might want to look at the
> > BeforeSave event, to try to stop them from saving.
> >
> >
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>

Hello Vasant,

Believe you are thinking of overwriting a file.

Application.displayAlerts = False
thisworkbook.SaveAs Filename:="C:\MyFolder\MyExistingFilename.xls"
Application.DisplayAlerts = True

In xl2003 help, at least, they point this exception out.


--
Regards,
Tom Ogilvy

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:[email protected]...
> Hi Tom:
>
> I'm probably wrong <g>, but I seem to remember that this is the one case
in
> which Excel does not perform the default action (despite what Help says).
>
> Regards,
>
> Vasant
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:u3C%[email protected]...
> > Just a heads up for consideration:
> > I didn't test this particular case, but help says displayalerts = false
> > pursues the default action of the dialog.
> > Using displayalerts would suppress the prompt, but the default action
> would
> > be taken and that is to save the file.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Darrin Henshaw" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Set Application.DisplayAlerts to False, to not show the save changes
> > > dialog box. However, that will stop Excel from prompting it, but still
> > > give the user the ability to save it. You might want to look at the
> > > BeforeSave event, to try to stop them from saving.
> > >
> > >
> > >
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> >
> >
>
>

Both of these solution worked perfectly--thanks!

"Alan Howells" wrote:

> You can use
> ThisWorkbook.Close SaveChanges:=False
> or
> Workbooks("File1").Close SaveChanges:=False
>
> "JDay01" <[email protected]> wrote in message
> news:[email protected]...
> > I am a self-taught user of VB for Excel and I've come along pretty well,
> but
> > I'm getting stuck on something that seems like it should be fairly simple
> to
> > do. I have two files: File1 contains a macro that performs various
> > functions on File2. When the File1 macro is finished, I want File1 to
> close
> > automatically WITHOUT prompting the user if they wish to save (this file
> > should never be saved by the user). What can I do to force Excel to
> close
> > the file without an option to save?
>
>
>

I'm sure you're right, Tom ... my memory's not what it used to be!

Regards,

Vasant

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Hello Vasant,
>
> Believe you are thinking of overwriting a file.
>
> Application.displayAlerts = False
> thisworkbook.SaveAs Filename:="C:\MyFolder\MyExistingFilename.xls"
> Application.DisplayAlerts = True
>
> In xl2003 help, at least, they point this exception out.
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:[email protected]...
> > Hi Tom:
> >
> > I'm probably wrong <g>, but I seem to remember that this is the one case
> in
> > which Excel does not perform the default action (despite what Help
says).
> >
> > Regards,
> >
> > Vasant
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:u3C%[email protected]...
> > > Just a heads up for consideration:
> > > I didn't test this particular case, but help says displayalerts =
false
> > > pursues the default action of the dialog.
> > > Using displayalerts would suppress the prompt, but the default action
> > would
> > > be taken and that is to save the file.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Darrin Henshaw" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > Set Application.DisplayAlerts to False, to not show the save changes
> > > > dialog box. However, that will stop Excel from prompting it, but
still
> > > > give the user the ability to save it. You might want to look at the
> > > > BeforeSave event, to try to stop them from saving.
> > > >
> > > >
> > > >
> > > >
> > > > *** Sent via Developersdex http://www.developersdex.com ***
> > >
> > >
> >
> >
>
>