Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Charlotte, NC

    Question Unanswered: TransferSpreadsheet to Dynamic Excel filename

    I have an Access 2000 database that I export several tables monthly to Excel spreadsheets using the TransferSpreadsheet in a macro. Each month after I export the tables to Excel, I then go to Windows Explorer and rename the files to add a suffix of MMYY (ie: 0904) to the names. Is there anyway to automate this when I export the tables.

  2. #2
    Join Date
    Sep 2004
    You can make this
    FileName = "Table" & Format(Now,"mm/yy")
    Docmd.TransferSpreadSheet acExport,8, "TbleAccess", Filename, etc...

  3. #3
    Join Date
    Feb 2010

    Adding Query Result to File Name

    Thank you for posting that answer. In addition to including the date in a filename, is there a way to include an sql query in the parameters for the file name?

    For example:
    ="C:\Folder\" & Format((select max([orderdate]) from SalesTable),"mm-dd-yyyy")&".xls"

    BTW, this didn't work.

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    You may find this example:


    All you do is save any of your queries with a prefix of "Export" (or change the criteria), and it let's the user view/export the query as the query name (including adding the date format), and the type of export (dbf, xls, csv). The view opens up in a form view so you code events around the form. It uses the same docmd.transferspreadsheet command and some simple tricks.

    Otherwise you need to create a variable and include the name of the query in your variable similar to jepi's example.
    Last edited by pkstormy; 02-06-10 at 19:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts