Using SaveAs Statement to overwrite existing File
 

Hi

I recently coded a spreadsheet which creates subsets of
data which are then saved separately. After cutting the
data I use the statement below to save the data:

ActiveWorkbook.SaveAs Filename:="D:\msoffice\access\work\"
& thisfilename & ".xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

However, the problem I have is that excel keeps stopping
at this command to get confirmation that it's okay to
overwrite an existing file with the same name with the new
one.

is there anything I can put in the code that effectively
stops excel checking to see if the filename already exists?

Thanks in advance for any postings.

Peter

Using SaveAs Statement to overwrite existing File
 

I put this line in to test for the existence of the new file before saving.

If Dir("D:\msoffice\access\work\" & thisfilename & ".xls") = "" Then
Kill("D:\msoffice\access\work\" & thisfilename & ".xls")
End if

You may also think about trapping error 70 (? or 75? from memory) to handle the situation where another user already has the file open that you are trying to Kill.

"Peter" wrote:

Hi

I recently coded a spreadsheet which creates subsets of
data which are then saved separately. After cutting the
data I use the statement below to save the data:

ActiveWorkbook.SaveAs Filename:="D:\msoffice\access\work\"
& thisfilename & ".xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

However, the problem I have is that excel keeps stopping
at this command to get confirmation that it's okay to
overwrite an existing file with the same name with the new
one.

is there anything I can put in the code that effectively
stops excel checking to see if the filename already exists?

Thanks in advance for any postings.

Peter

Using SaveAs Statement to overwrite existing File
 

wouldn't the check be

If Dir("D:\msoffice\access\work\" & thisfilename & ".xls") < "" Then
Kill("D:\msoffice\access\work\" & thisfilename & ".xls")
End if

--
Regards,
Tom Ogilvy

"Trevor Mills" wrote in message
...
I put this line in to test for the existence of the new file before
saving.

If Dir("D:\msoffice\access\work\" & thisfilename & ".xls") = "" Then
Kill("D:\msoffice\access\work\" & thisfilename & ".xls")
End if

You may also think about trapping error 70 (? or 75? from memory) to
handle the situation where another user already has the file open that you
are trying to Kill.

"Peter" wrote:

Hi

I recently coded a spreadsheet which creates subsets of
data which are then saved separately. After cutting the
data I use the statement below to save the data:

ActiveWorkbook.SaveAs Filename:="D:\msoffice\access\work\"
& thisfilename & ".xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

However, the problem I have is that excel keeps stopping
at this command to get confirmation that it's okay to
overwrite an existing file with the same name with the new
one.

is there anything I can put in the code that effectively
stops excel checking to see if the filename already exists?

Thanks in advance for any postings.

Peter

Using SaveAs Statement to overwrite existing File
 

Another approach:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"D:\msoffice\access\work\" _
& thisfilename & ".xls", _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False


--
Regards,
Tom Ogilvy


"Peter" wrote in message
...
Hi

I recently coded a spreadsheet which creates subsets of
data which are then saved separately. After cutting the
data I use the statement below to save the data:

ActiveWorkbook.SaveAs Filename:="D:\msoffice\access\work\"
& thisfilename & ".xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

However, the problem I have is that excel keeps stopping
at this command to get confirmation that it's okay to
overwrite an existing file with the same name with the new
one.

is there anything I can put in the code that effectively
stops excel checking to see if the filename already exists?

Thanks in advance for any postings.

Peter