Results 1 to 10 of 10

Exporting Excel Files from Access 2007 Using Macros/VBA

  1. #1
    JoeM is offline Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,759

    Exporting Excel Files from Access 2007 Using Macros/VBA

    I have some VBA code in an Access 2007 database that exports data to an Excel file (.xlsx extension) using the TransferSpreadsheet command.
    I have noticed that with all files created by this process, if someone is to open the file and edit it, a "backup" of the file is automatically created (with the name "Backup of filename.xlk"). I really don't want these backups out there mucking up our folders.

    Is there some way to prevent Access from creating the files this way? It does not appear to be any argument in the TransferSpreadsheet command.

    Thanks
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,206
    I don't think Access is doing it:

    http://www.mrexcel.com/forum/excel-q...matically.html
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    JoeM is offline Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,759
    I don't think Access is doing it:
    Actually, it is. It is very apparent in the work environment I am in. I work with a lot of Excel files and Access databases, creating lots of new ones every day. The only files that get these backup files are Excel files that are created from Access. It does not seem to matter whether you use the TransferSpreadsheet or OutputTo options.

    I have been researching a number of different forums on this topic, and there seems to be a lot of inaccurate and incomplete information on it. If you read through enough posts on it, you can piece it all together. Yes, there is a workbook setting in Excel where you can shut this off, but it is workbook specific, and you can only do this on these Access to Excel files after the Excel file has been created. It appears that by default, Access creates Excel files this way, and there isn't any way to change that.

    So, it appears that these are some of my options:
    1. Change the setting in Excel after the file is created (not really feasible for me, as multiple files are created through automated processes every week);
    2. Create some VBA code or other automated process to periodically "clean-up/delete" these backup files.

    Neither one is really too appealing to me. I was hoping for a more preventitive solution, but it appears that may not be possible.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,206
    My point is that Access isn't creating the backup, Excel is. It may be that files created through Access get that option checked and others don't. If you have automated processes creating the files, can't you simply manipulate that setting in that process? A quick test shows the option can be set here:

    Code:
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\PBaldy\Documents\Book1.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    JoeM is offline Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,759
    My point is that Access isn't creating the backup, Excel is. It may be that files created through Access get that option checked and others don't.
    True.
    If you have automated processes creating the files, can't you simply manipulate that setting in that process? A quick test shows the option can be set here:
    The code you posted there looks like Excel VBA, not Access VBA. The process from Access VBA that actually creates the files uses the "TransferSpreadsheet" or "OutputTo" commands. So it appears you cannot create the Excel files form Access without this automatic backup set, though there are way "after the fact" to open up the Excel file and change the setting (but not until the file actually exists).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,206
    That is straight from Excel, but it should be easy to convert. I wonder if specifying an older Excel format in your existing code would change anything? I'm on an iPad right now, so can't test.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    JoeM is offline Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,759
    That is straight from Excel, but it should be easy to convert. I wonder if specifying an older Excel format in your existing code would change anything? I'm on an iPad right now, so can't test.
    I am not sure I am following your line of reasoning or what you are proposing. You cannot do a "SaveAs" command on an Access query to make it an Excel file. Are you proposing exporting the Access query to Excel using the "Transferspreadsheet" or "OutputTo" commands, then opening it via VBA and changing the settings then?

    I wonder if specifying an older Excel format in your existing code would change anything?
    Eureka! I think you have stumbled upon something here. To see what would happen, I tried exporting the files to "Excel 97-2003 (.xls)" instead of "Excel 2007 (.xlsx)" format, and it doesn't seem to make backups when you edit those xls files. So I think I can simply changing the Excel formatting option to avoid this issue.

    Thanks!
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,206
    Happy to help. Thought that might work, since it's a newer feature.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,993
    Joe and Paul.

    I recently had this same issue and found this thread that solved my problem and allowed me to export to .xlsx and not have the auto backup in Excel.

    http://www.accessforums.net/import-e...nce-15655.html
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  10. #10
    JoeM is offline Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,759
    I recently had this same issue and found this thread that solved my problem and allowed me to export to .xlsx and not have the auto backup in Excel.
    Thanks. I am currently using the workaround mentioned in my previous post, but it is always good to have multiple options, as I am sure it will come up again on future projects.

    Thanks
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. Replies: 10
    Last Post: 12-28-2012, 12:06 PM
  2. Run Excel Macros in Access
    By jo15765 in forum Programming
    Replies: 8
    Last Post: 10-13-2011, 12:20 PM
  3. Last Record in macros ACCESS 2007
    By jcsuarez in forum Programming
    Replies: 2
    Last Post: 07-09-2011, 05:24 PM
  4. Using Excel macros in Access
    By Lorlai in forum Programming
    Replies: 2
    Last Post: 06-03-2011, 01:01 PM
  5. Replies: 1
    Last Post: 02-21-2011, 07:55 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
  •  
Tech Forums: Microsoft Office Forums