SaveAs problem in Excel VBA script

[joannele71]

Hi

I have the following macro to save the Index sheet as a new workbook "abc"
in csv format. Since I have the abc.csv file in the c:\ directory, Excel
will ask me whether I want to overwrite the file. So I have the
application.sendkeys to say Yes to overwrite. This works fine if I am
running it manually but the macro stops at the overwrite question if I
scheduled the macro to run. Then I have to hit yes and the macro finishes it
up. Do you know how I can fix this issue? Thank you in advance.

Joanne

Sheets("Index").Select
Sheets("Index").Copy

Application.SendKeys ("Y~")

ActiveWorkbook.SaveAs Filename:="c:\abc.csv", _
FileFormat:=xlCSV, CreateBackup:=False

ActiveWindow.Close

 

[ward376]

Put application.displayalerts = false before the saveas line and
application.displayalerts = true after the line.

Cliff Edwards

 

[ward376]

Since you're closing the new workbook, return displayalerts to true
after that action.

Sub copySheet()
Sheets("Index").Copy

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\abc.csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True

End Sub

Cliff Edwards

 

[joannele71]

Hi Cliff,

That works... thank you very much.

Joanne