Results 1 to 3 of 3
  1. #1
    mp3909 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    136

    DoCmd.TransferSpreadsheet


    I wanted to know if an already existing file with the same location and filename as that specified in the
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetExcel12Xml, tableName, filename
    , gets either replaced or does a new one get created?

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It will automatically overwrite the file contents.
    Be warned though it doesn't create a new file.

    As an example you export to a sheet with 50 rows. You go and save some calculations (formulas) at the bottom of the data.
    You save those changes.

    Later you do another automated export - only 45 rows. It all works swimmingly. You open the sheet - and your formatting and formulas are still there at row 51 .
    No problem.

    Now you do another export. this time 70 rows.
    You'll get an error.
    When it reaches row 51 access will barf, as the cells already have info in them that doesn't match the formatting of the data being exported.

    You'll be confused.
    About 2 hours later you'll work it out if you are lucky.

    So you have to handle this, and give users option to delete and overwrite the existing version, or keep their formatting and risk the error.
    Also check the file isn't already open when attempting the transfer, another error is raised.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you do want a new file every time and not to overwrite any existing files, add a date/time stamp to your file name, i.e.
    Code:
    filename = path & file name prefix & Format(Now(), "yyyymmdd_hhmmss") & ".xlsx"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-07-2019, 01:27 PM
  2. DOcmd.Transferspreadsheet with a Table name
    By mcucino in forum Programming
    Replies: 1
    Last Post: 08-01-2018, 01:20 PM
  3. DoCmd.TransferSpreadsheet skipping columns
    By Sephaerius in forum Modules
    Replies: 4
    Last Post: 07-31-2018, 11:58 AM
  4. Docmd.Transferspreadsheet
    By RayMilhon in forum Programming
    Replies: 5
    Last Post: 06-30-2017, 08:49 PM
  5. How do I use DoCmd.TransferSpreadsheet acExport
    By morerockin in forum Access
    Replies: 6
    Last Post: 09-24-2015, 03:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums