Results 1 to 3 of 3

Export one query to Excel multiple workbooks with multiple sheets

  1. #1
    fastsue is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018

    Export one query to Excel multiple workbooks with multiple sheets


    I want to be able to export a query to Excel workbooks with multiple excel sheets.
    Workbook name to be from the query (FileNameXls) and the sheet name by Variety i.e. Apple, Pear - as outlined in table below to a destination folder K:\FarmResults

    I have a query that gathers together results for different clients(farm) and is sorted by farm, variety, plot then date. Query is called qry_results

    IdNo Farm Variety Plot SampDt Result FileNameXls
    1 FarmA Apple Plot1 01/08/19 200 FarmA-FM-Date.xls
    1 FarmA Apple Plot1 08/08/19 175 FarmA-FM-Date.xls
    1 FarmA Pear Plot2 01/08/19 150 FarmA-FM-Date.xls
    1 FarmA Pear Plot2 08/08/19 50 FarmA-FM-Date.xls
    2 FarmB Plum Plot3 01/08/19 36 FarmB-FM-Date.xls
    2 FarmB Plum Plot3 08/08/19 72 FarmB-FM-Date.xls
    2 FarmB Cherry Plot4 01/08/19 48 FarmB-FM-Date.xls
    2 FarmB Cherry Plot4 08/08/19 54 FarmB-FM-Date.xls
    I have a similar query based on qry_results called qry_results_groupby that groups by idno, to get one result per farm.

    I use these queries to produce a pdf from a report (i have another column with FileNamePDF) and it works really well and produces reports by farm etc. via a button on form - code supplied by accessforum some years ago, uses DoCmd.OutputTo acFormatPDF etc.

    I want to use the same data to export to the excel workbooks from another button. I have tried to search but can't find a thread that helps me!!

    Any help will be gratefully received.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    make a listbox of the 'fruits'.
    code will cycle thru the list, and export only those items selected.
    the query uses the listbox as a parameter to pull only those items.

    the query,qsExport1Fruit, would be:
    select * from table where [fruit]=forms!myForm!lstbox

    the code would cycle all via:
    vFile = "c:\myFruits.xls"
    For i = 0 To lstBox.ListCount - 1
       vItm = lstBox.ItemData(i)   'get next item in list
      lstBox = vItm                'set list to this item
         'export the item
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsExport1Fruit", vFile, True, vItm

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Anchorage, Alaska, USA
    I'm not real clear on how you want Excel workbooks to be named.

    It sounds like you want an Excel workbook for each farm by date, with a worksheet for each variety (with "date" in the file name replaced with the SampDt).
    Maybe something like:

    File Name = FarmA-FM-01/08/19.xls
    2 worksheets - Apple & Pear

    File Name = FarmB-FM-01/08/19.xls
    2 worksheets - Plum & Cherry


    File Name = FarmA-FM-08/08/19.xls
    2 worksheets - Apple & Pear

    File Name = FarmB-FM-08/08/19.xls
    2 worksheets - Plum & Cherry

    Maybe Ken Snell's site will help (VBA code examples to write from Access to Excel)
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 4
    Last Post: 01-12-2017, 01:29 AM
  2. Replies: 3
    Last Post: 04-18-2015, 05:24 PM
  3. Replies: 2
    Last Post: 10-15-2014, 12:30 PM
  4. Replies: 12
    Last Post: 12-17-2012, 12:47 PM
  5. Export Table in Access 2007 to Multiple Workbooks in Excel 2007
    By hutchinsm in forum Import/Export Data
    Replies: 5
    Last Post: 03-01-2012, 05:23 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