Save Macro - yet another question
 

How do I get the macro to answer "yes" automatically when the Excel dialog
box comes up with the message "file name already exists - do you want to
overwrite?"

The reason is becasue I've got a number of files to save automatically and i
don't want the user to have to keep hitting the "y" key.

--
Roger

Roger, use something like this

Application.DisplayAlerts = False
'Your Save 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 2002 & 2003

"Roger" wrote in message
...
How do I get the macro to answer "yes" automatically when the Excel dialog
box comes up with the message "file name already exists - do you want to
overwrite?"

The reason is becasue I've got a number of files to save automatically and
i
don't want the user to have to keep hitting the "y" key.

--
Roger

Roger,

Two ways to accomplish this (see below)

The neatest way is to use "Application.DisplayAlerts=False" command before
your save commands

Alternatively, you can use SENDKEYS to send a "Y" (or whatever character you
want) before your save commands.

Sub SaveAll()
Application.DisplayAlerts = False
'BookName1 = "D:\Pete's Operations\Impacts\Impacts Database - " &
Format(Date, "yy-mm-dd") & ".xls"
'BookName2 = "F:\Systems Management\Impacts Database - " & Format(Date,
"yy-mm-dd") & ".xls"
BookName1 = "D:\Pete's Operations\01 Processes\Impacts Database.xls"
BookName2 = "F:\Systems Management\02 Impacts Database\Impacts
Database.xls"

'SendKeys "y"
'ChDir "D:\Pete's Operations\01 Processes"
ActiveWorkbook.SaveAs Filename:=BookName1, _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'SendKeys "y"
'ChDir "F:\Systems Management\02 Impacts Database"
ActiveWorkbook.SaveAs Filename:=BookName2, _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Hope one of these helps

Pete



"Roger" wrote:

How do I get the macro to answer "yes" automatically when the Excel dialog
box comes up with the message "file name already exists - do you want to
overwrite?"

The reason is becasue I've got a number of files to save automatically and i
don't want the user to have to keep hitting the "y" key.

--
Roger

thanks for that

"Peter Rooney" wrote:

Roger,

Two ways to accomplish this (see below)

The neatest way is to use "Application.DisplayAlerts=False" command before
your save commands

Alternatively, you can use SENDKEYS to send a "Y" (or whatever character you
want) before your save commands.

Sub SaveAll()
Application.DisplayAlerts = False
'BookName1 = "D:\Pete's Operations\Impacts\Impacts Database - " &
Format(Date, "yy-mm-dd") & ".xls"
'BookName2 = "F:\Systems Management\Impacts Database - " & Format(Date,
"yy-mm-dd") & ".xls"
BookName1 = "D:\Pete's Operations\01 Processes\Impacts Database.xls"
BookName2 = "F:\Systems Management\02 Impacts Database\Impacts
Database.xls"

'SendKeys "y"
'ChDir "D:\Pete's Operations\01 Processes"
ActiveWorkbook.SaveAs Filename:=BookName1, _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'SendKeys "y"
'ChDir "F:\Systems Management\02 Impacts Database"
ActiveWorkbook.SaveAs Filename:=BookName2, _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Hope one of these helps

Pete



"Roger" wrote:

How do I get the macro to answer "yes" automatically when the Excel dialog
box comes up with the message "file name already exists - do you want to
overwrite?"

The reason is becasue I've got a number of files to save automatically and i
don't want the user to have to keep hitting the "y" key.

--
Roger

thanks for that

"Paul B" wrote:

Roger, use something like this

Application.DisplayAlerts = False
'Your Save 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 2002 & 2003

"Roger" wrote in message
...
How do I get the macro to answer "yes" automatically when the Excel dialog
box comes up with the message "file name already exists - do you want to
overwrite?"

The reason is becasue I've got a number of files to save automatically and
i
don't want the user to have to keep hitting the "y" key.

--
Roger