Override massage: do you want to replace existing report

[sofi77]

I created a Macro to automate saving each tab to one of my folders.
This is an example of one of the steps ( I have 20 tabs and the same process is repeated for each one):

Sheets("790500001").Select
Sheets("790500000").Copy
ActiveWorkbook.SaveAs Filename:= _
"J:\Investor_Reporting\IR 2011\Dat A .CSV and .XLS files to Trustees and MS\Remit GE.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

When I run the Macro, after the end of every step, I get a prompt asking if I want to replace the existing file. the answer is always going to be "yes" so how do modify the code to stop these prompts.

I would really appreciate your help.

Thank you

 

[Andrzejek]

Did you try[tt]
DisplayAlerts = False[/tt]
at the top of your code?

Have fun.

---- Andy

 

[sofi77]

I think I need a little more help. I am brand new to VBA.
so I added: DiplayAlerts= False as shown below:

DisplayAlerts = False
Sheets("790500001 ").Select
Sheets("790500001 ").Copy
ActiveWorkbook.SaveAs Filename:= _
"J:\Investor_Reporting\IR 2011\Dat A .CSV and .XLS files to Trustees and MS\.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

but when I run the Macro it does not seem like it's capturing the change. it's still gives me that same prompt. Am I doing something wrong?

Thank you so much for your help Andy

 

[Andrzejek]

Are you saving your Excel file as a new file with a new name? If so, what IS the new name for your worbook?

If not, and you just Save the file, whay do you use Save US?


Have fun.

---- Andy

 

[PHV]

What about this:

Sheets("790500000").Copy
strFileName = "J:\Investor_Reporting\IR 2011\Dat A .CSV and .XLS files to Trustees and MS\Remit GE.xls"
If Dir(strFileName) <> "" Then Kill strFileName
ActiveWorkbook.SaveAs Filename:=strFileName _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sofi77]

Yes, I am saving each tab as a new file in a different forder and with a different name,that's why I am using save as.

 

[endrefi]

Hi sofi77,

the full syntax would be:

Application.DisplayAlerts = False

it works for me all the time: no questions asked by any application (not even when I run a Word macro called from Excel)