Overwriting Excel workbooks

[Orlando Acevedo]

I'm writing a macro that opens several workbooks, transforms them and then
saves them as a text files.
The problem is that I need the same files overwritten, and when I use the
line below, I get a message saying that a file with the same name already
exists and asks if I want to replace it, which means that I can't automate
the process because I have to click on 'yes' after each file is ready.
Is there a way to automate this?

ActiveWorkbook.SaveAs Filename:= strPath & "\" & strFileName(i) & ".txt",
FileFormat:=xlText

Thank you.

 

[Andy Pope]

Hi,

You can over ride the message box warning by using the following,

Application.DisplayAlerts = False ' suppress warning
ActiveWorkbook.SaveAs Filename:= strPath & "\" & strFileName(i) _
& ".txt",FileFormat:=xlText
Application.DisplayAlerts = True

Of course you need to be aware of the implication of this, which is it
will now overwrite the file without warning.

Cheers
Andy

 

[Karl E. Peterson]

I'm writing a macro that opens several workbooks, transforms them and
then saves them as a text files.
The problem is that I need the same files overwritten, and when I use
the line below, I get a message saying that a file with the same name
already exists and asks if I want to replace it, which means that I
can't automate the process because I have to click on 'yes' after
each file is ready.
Is there a way to automate this?

ActiveWorkbook.SaveAs Filename:= strPath & "\" & strFileName(i) &
".txt", FileFormat:=xlText

You could also Kill the existing TXT file before doing the SaveAs.

 

[Steve Rindsberg]

I'm writing a macro that opens several workbooks, transforms them and then
saves them as a text files.
The problem is that I need the same files overwritten, and when I use the
line below, I get a message saying that a file with the same name already
exists and asks if I want to replace it, which means that I can't automate
the process because I have to click on 'yes' after each file is ready.
Is there a way to automate this?

Like so, maybe?

Dim strFullPath as String
strFullPath = strPath & "\" & strFileName(i) & ".txt"

If Len(Dir(strFullPath)) > 0 Then
' the file exists, delete it
Kill(strFullPath)
End If
ActiveWorkbook.SaveAs Filename:= strFullPath, FileFormat:=xlText