Saving an Excel worksheet

Discussion in 'Microsoft Access External Data' started by Marius from Rome, Jan 7, 2004.

  1. My Access application opens an existing Excel worksheet and inserts data in
    it.
    My application should give my worksheet a new file name (i.e.
    newworksheet.xls), save and close it.
    I attempted using the .saveas and .close methods, but if a file with the
    same name altrady exists I don't get any error while the user is prompted to
    save changes in the original file.
    Can anybody please suggest the best and simplest way to check if a file with
    the new name already exists and/or to overwrite it?
    Regards
    marius
     
    Marius from Rome, Jan 7, 2004
    #1
    1. Advertisements

  2. Marius from Rome

    John Nurick Guest

    Hi Marius,

    You can use the Dir() function, something like this:

    Dim strNewName as String

    strNewName = "newworksheet.xls"
    With MyWorksheet
    If Len(Dir(.Path & "\" & strNewName)) > 0 Then
    'File already exists
    Kill .Path & "\" & strNewname
    End If
    .SaveAs strNewName
    .Close
    End With


    On Wed, 7 Jan 2004 17:23:38 +0100, "Marius from Rome"
    <> wrote:

    >My Access application opens an existing Excel worksheet and inserts data in
    >it.
    >My application should give my worksheet a new file name (i.e.
    >newworksheet.xls), save and close it.
    >I attempted using the .saveas and .close methods, but if a file with the
    >same name altrady exists I don't get any error while the user is prompted to
    >save changes in the original file.
    >Can anybody please suggest the best and simplest way to check if a file with
    >the new name already exists and/or to overwrite it?
    >Regards
    >marius
    >
    >


    --
    John Nurick [Microsoft Access MVP]

    Please respond in the newgroup and not by email.
     
    John Nurick, Jan 7, 2004
    #2
    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. pooja

    exporting report output in footer to Excel worksheet

    pooja, Jun 24, 2003, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    1,291
    pooja
    Jun 24, 2003
  2. Levi

    export to specific excel worksheet

    Levi, Jun 26, 2003, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    1,142
    Richard Choate
    Jun 28, 2003
  3. John G

    Can not open Excel file after saving it in 97/2000

    John G, Mar 17, 2004, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    207
    John G
    Mar 17, 2004
  4. CBB1333

    Saving radio button results from excel into Access

    CBB1333, Apr 6, 2004, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    206
    CBB1333
    Apr 6, 2004
  5. Guest

    Opening/Saving Excel Shared Workbooks from Access

    Guest, Jul 25, 2005, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    234
    Guest
    Jul 25, 2005
  6. Guest

    Saving Xport to Excel file

    Guest, Nov 12, 2005, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    230
    John Nurick
    Nov 14, 2005
  7. Guest

    Opening a .dat file in Excel, then saving as .xls, all From Access

    Guest, Sep 15, 2006, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    1,309
    John Nurick
    Sep 15, 2006
  8. Phil Smith

    TransferSpreadsheet to existing worksheet creates new worksheet instead.

    Phil Smith, Mar 20, 2009, in forum: Microsoft Access External Data
    Replies:
    4
    Views:
    3,548
    Ken Snell MVP
    Mar 24, 2009
Loading...