Using SaveAs Statement to overwrite existing File

Discussion in 'Microsoft Excel Programming' started by Peter, Jun 16, 2004.

  1. Peter

    Peter Guest

    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
     
    Peter, Jun 16, 2004
    #1
    1. Advertisements

  2. Peter

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

    "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
    >
     
    Guest, Jun 16, 2004
    #2
    1. Advertisements

  3. Peter

    Tom Ogilvy Guest

    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
    news:...
    > 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
    > >
     
    Tom Ogilvy, Jun 16, 2004
    #3
  4. Peter

    Tom Ogilvy Guest

    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
    news:1d59101c4538f$15feb7e0$...
    > 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
     
    Tom Ogilvy, Jun 16, 2004
    #4
  5. Peter

    1plus19

    Joined:
    Mar 26, 2010
    Messages:
    2
    Likes Received:
    0
    1plus19, Mar 26, 2010
    #5
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. susie

    overwrite Excel SaveAs function from File menu

    susie, Jul 30, 2003, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    353
    Dave Peterson
    Jul 31, 2003
  2. Peter Pham

    Method SaveAs with no overwrite YesNoCancel dialog

    Peter Pham, Jul 16, 2004, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    283
    Bob Umlas
    Jul 16, 2004
  3. jonahviakeyboard

    Prevent PivotTable Overwrite Warning and Disallow Overwrite

    jonahviakeyboard, Nov 27, 2007, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    783
    jonahviakeyboard
    Nov 27, 2007
  4. Ed from AZ

    Allowing xlBook.SaveAs in Access code to overwrite existing workbook?

    Ed from AZ, Jan 26, 2008, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    3,055
    Dave Peterson
    Jan 26, 2008
  5. Bill Armitage

    Excel SaveAs to force overwrite of extant file

    Bill Armitage, Feb 12, 2010, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    704
    Gord Dibben
    Feb 12, 2010
Loading...

Share This Page