Results 1 to 4 of 4
  1. #1
    nyneave is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    24

    New workbook/worksheets using Transfer Spreadsheet

    I am trying to use the Transfer Spreadsheet to create a new workbook with multiple spreadsheets. The code I am using is as follows:
    Private Sub cmd_fruits_Click()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "qry_apples", "H:\Fruits\Test_Files\FRUIT_LIST" + Format(Now, "YYYY-MM-DD") + ".xls", True
    End Sub

    This works to create a new workbook with the date; however, it names the tab "FRUIT_LIST_YYYY-MM-DD". I have about 20 queries and I would like them to create and update the workbook with a tab, labeled for each query. I found the instruction online to put the worksheet name in the "Range" which I tried.

    When I do the following and the worksheet has not been created, it still creates the workbook with the worksheet name of FRUIT_LIST_YYYY_MM_DD:

    Private Sub cmd_fruits_Click()


    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "qry_apples", "H:\Fruits\Test_Files\FRUIT_LIST" + Format(Now, "YYYY-MM-DD") + ".xls", True, Apples!
    End Sub

    When I do the same and the workbook already exists (with a pre-named worksheet of "Apples"); it update the workbook, but overwrites the worksheet name again with FRUIT_LIST_YYYY_MM_DD.

    Can someone please help? I would like to create 1 workbook FRUIT_LIST YYYY-MM-DD.xls; with multiple worksheets (ie...apples, bananas, peaches, etc...) - each being created from a pre-defined query in Access. The first query should create the workbook (run daily) and the remainder add a new worksheet to the same workbook with a "defined name".

    Thanks
    nyneave

  2. #2
    nyneave is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    24
    PS...I am in a Access 2003 database - with Excel 2003 SP3...

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "qry_apples", "H:\Fruits\Test_Files\FRUIT_LIST" + Format(Now, "YYYY-MM-DD") + ".xls", True
    You should be using the ampersand to concatenate text strings and not the plus:
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,  "qry_apples", "H:\Fruits\Test_Files\FRUIT_LIST" & Format(Now,  "YYYY-MM-DD") & ".xls", True


    Ken Snell has a site that might help.
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

    There are examples on importing and exporting from/to Excel. On the exporting page, it sounds like you are looking for the third example:

    Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate Worksheets within one EXCEL file via TransferSpreadsheet (VBA)


    Also, read the last section titled:
    Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)

  4. #4
    nyneave is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    24
    Sorry, I had ampersands; just overwrote them when I changed the data to "junk". I got it working with the link you gave me - thanks so much!

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

Similar Threads

  1. Transfer Spreadsheet
    By mulefeathers in forum Import/Export Data
    Replies: 1
    Last Post: 05-02-2012, 12:52 PM
  2. Replies: 1
    Last Post: 03-12-2012, 02:21 PM
  3. Deleting Worksheets on a Excel workbook
    By BED in forum Programming
    Replies: 0
    Last Post: 07-27-2010, 01:20 PM
  4. Button Command to Transfer Spreadsheet
    By diane802 in forum Programming
    Replies: 3
    Last Post: 02-10-2010, 09:26 AM
  5. Transfer SpreadSheet Command and Rename
    By fintan06 in forum Import/Export Data
    Replies: 2
    Last Post: 10-15-2009, 08:00 AM

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