Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting rid of Prompts in a macro
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting rid of Prompts in a macro
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 ** "Duncan J" wrote in message ... 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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting rid of Prompts in a macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting rid of Prompts in a macro
ActiveWorkbook.Close
ActiveWorkbook.Close ActiveWorkbook.Close ActiveWorkbook.Close False Rob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting rid of Prompts in a macro
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting rid of Prompts in a macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel macro which prompts for input and moves to a cell - repeated | Excel Worksheet Functions | |||
Macro within a macro and Macro Prompts | Excel Discussion (Misc queries) | |||
How to disable macro prompts for OK in Excel? | Excel Discussion (Misc queries) | |||
Prompts | Excel Discussion (Misc queries) | |||
How can I suppress prompts during macro run? | Excel Discussion (Misc queries) |