Using SaveAs Statement to overwrite existing File

Using SaveAs Statement to overwrite existing File

Post by Pete » Thu, 17 Jun 2004 19:45:48


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

Post by VHJldm9yIE » Thu, 17 Jun 2004 19:59:01

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.

 
 
 

Using SaveAs Statement to overwrite existing File

Post by Tom Ogilv » Thu, 17 Jun 2004 20:25:43

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



saving.
handle the situation where another user already has the file open that you
are trying to Kill.
 
 
 

Using SaveAs Statement to overwrite existing File

Post by Tom Ogilv » Thu, 17 Jun 2004 20:27:48

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