Getting rid of Prompts in a macro

[Duncan J]

Hi Folks,
I've got a big macro, several tied into one. However, when running the macro the prompts always come up one asks do you want to save, "YES" "NO" which I'd like it automatically save with out the prompt. Then another on comes on I don't want to save. Here's the code.
ActiveWorkbook.SaveAs Filename:="C:\otd\data\compile\International.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
ActiveWorkbook.Close
I suppose when closing the active workbook the prompt comes up asking me to save and I don't want to save the second one. But also do not want the prompt to show.
Thanks again...

 

[Paul B]

Duncan, try Application.DisplayAlerts = False, this Want show display
alerts, turn it back on at the end of you code
something like this

Application.DisplayAlerts = False
'Your Code
Application.DisplayAlerts = True

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

Hi Folks,
I've got a big macro, several tied into one. However, when running the

macro the prompts always come up one asks do you want to save, "YES" "NO"
which I'd like it automatically save with out the prompt. Then another on
comes on I don't want to save. Here's the code.

ActiveWorkbook.SaveAs Filename:="C:\otd\data\compile\International.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
ActiveWorkbook.Close
I suppose when closing the active workbook the prompt comes up asking me

to save and I don't want to save the second one. But also do not want the
prompt to show.

 

[arno]

Hi Duncan,

you just need a line:

ActiveWorkbook.SaveAs Filename:="C:\otd\data\compile\International.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Saved = true

ActiveWorkbook.Close

ActiveWorkbook.Saved = true

ActiveWorkbook.Close

do not use
application.displayalerts = false
unless you know what you're doing! Saved=true will do the job for you.

arno

 

[Rob]

ActiveWorkbook.Close

ActiveWorkbook.Close

ActiveWorkbook.Close
ActiveWorkbook.Close False

Rob

 

[Duncan J]

Thanks Guys
I used arno's code. Now I only get the first pop up saying a file already exists in this location. DO you want to replace i
"yes" "No" The Other pop up is gone. tried robs and that did nothing. Used Pauls and of course no pop up's on that
anymore suggestions on the first pop up
ActiveWorkbook.SaveAs Filename:="C:\otd\data\compile\Domestic.xls", FileFormat:=
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
, CreateBackup:=Fals
ActiveWorkbook.Saved = Tru
ActiveWorkbook.Clos
ActiveWorkbook.Saved = Tru
ActiveWorkbook.Clos

 

[arno]

The Code would work if the file wasn't thier. However, I need it to
overwrite the file.

use
application.displayalerts = false/true immediately before/after the critical
line (only where you really need it). do not use it for the whole code as
you might miss some "real" error messages.

arno