PC Review


Reply
Thread Tools Rate Thread

Using SaveAs Statement to overwrite existing File

 
 
Peter
Guest
Posts: n/a
 
      16th Jun 2004
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?VHJldm9yIE1pbGxz?=
Guest
Posts: n/a
 
      16th Jun 2004
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
>

 
Reply With Quote
 
 
 
 
Tom Ogilvy
Guest
Posts: n/a
 
      16th Jun 2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> >



 
Reply With Quote
 
Tom Ogilvy
Guest
Posts: n/a
 
      16th Jun 2004
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" <(E-Mail Removed)> wrote in message
news:1d59101c4538f$15feb7e0$(E-Mail Removed)...
> 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



 
Reply With Quote
 
New Member
Join Date: Mar 2010
Posts: 2
 
      26th Mar 2010
Excellent tutorial here that show you how to do the saveas function http://www.itjungles.com/other/conve...sing-vb-script
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel SaveAs to force overwrite of extant file Bill Armitage Microsoft Excel Programming 1 12th Feb 2010 11:12 PM
Allowing xlBook.SaveAs in Access code to overwrite existing workbook? Ed from AZ Microsoft Excel Programming 2 26th Jan 2008 09:16 PM
Prevent PivotTable Overwrite Warning and Disallow Overwrite jonahviakeyboard Microsoft Excel Programming 0 27th Nov 2007 06:08 PM
FP03 publish to overwrite or not to overwrite =?Utf-8?B?RGVsdGFHYW1tYQ==?= Microsoft Frontpage 3 27th Oct 2004 01:43 AM
overwrite Excel SaveAs function from File menu susie Microsoft Excel Programming 1 31st Jul 2003 04:47 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:52 PM.