Using SaveAs Statement to overwrite existing File

P

Peter

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
 
G

Guest

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.
 
T

Tom Ogilvy

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 said:
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.
 
T

Tom Ogilvy

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top