Results 1 to 7 of 7
  1. #1
    fastsue is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    3

    Export one query to Excel multiple workbooks with multiple sheets

    Hello
    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.
    Sue

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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:
    Code:
    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
    Next
    


  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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)
    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

  4. #4
    fastsue is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    3
    Thank you both for your replies.
    I stepped back from the problem and realised that I don't need to date the workbooks, just name them with the farm name as they will be overwritten each time there are new results.
    This is my original code, adapted from what I used to send data to a report. I couldn't get the code to run pass DoCmd.OpenQuery (highlighted in red). Is DoCmd.Transferspreadsheet a better way of exporting my query to Excel? I have not used this command before! How does the code suggested by Ranman256 fit into this?
    I looked at Ken Snells site but none of the solutions showed multiple workbooks that contain multiple sheets only multiple sheets in one workbook.

    Private Sub Command84_Click()
    'Set Variables and Objects to export results to Excel


    strQueryName = "qry_results"
    strSQL = "SELECT Idno, Farm, FileNameXLS From qry_results_groupby"
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(strSQL)

    If rst.EOF Then ' no records found
    MsgBox "No records found"
    rst.Close
    Set rst = Nothing
    Exit Sub
    End If


    rst.MoveFirst

    Do While Not rst.EOF
    lngGrowID2 = rst!Idno
    lngFarm2 = rst!Farm
    strFileNm2 = rst!FileNameXLS
    destFileNm2 = "K:\TreeRipe\GMExcel"

    DoCmd.OpenQuery strQueryName, acViewNormal, , "Idno=" & lngGrowID2, acHidden
    DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, destFileNm2 & "" & strFileNm2, False
    DoCmd.Close acQuery, strQueryName
    rst.MoveNext
    Loop
    MsgBox "Excel Workbooks done. They can be found in K:\TreeRipe\GMExcel", vbInformation


    'Recordset Cleanup
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing


    End Sub

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is what I did:

    I created a table named qry_Results and pasted your data into that table to simulate the source query.

    I created a module and created a public function called "EXPORTDATA"
    I added a reference to excel in the VBA window but there's no reason you can't do this with late binding I don't think.
    I ran the code below
    it creates to files named FarmA.xlsx and FarmB.xlsx
    each file has a tab for each fruit, each fruit has a list of the items from your result query

    bear in mind you will have to create a query called 'exporttemp' to start with as this code expects the query to be there. YOu'll have to so some addtional error trapping but the basic function is there. it creates the files in the same folder as the database itself but you can also modify that.

    Code:
    Dim rstFarm, rstVariety, rstData
    Dim sFileName, sFarm, sVariety
    Dim wbk, wks
    Dim ExcelApp
    
    
    Set rstFarm = CurrentDb.OpenRecordset("SELECT Farm FROM qry_Results GROUP BY Farm ORDER BY Farm")
    If rstFarm.RecordCount > 0 Then
        Set ExcelApp = CreateObject("excel.Application", "")
        ExcelApp.Visible = False
        Do While rstFarm.EOF <> True
            sFarm = rstFarm!farm
            sFileName = Replace(CurrentProject.Path & "\", "\\", "\") & sFarm & ".xlsx"
            If Len(Dir(sFileName)) > 0 Then
                Kill sFileName
                DoEvents
            End If
            Set wbk = ExcelApp.workbooks.Add
            wbk.saveas sFileName
            wbk.Close
            Set rstVariety = CurrentDb.OpenRecordset("SELECT Variety FROM qry_Results WHERE Farm = '" & sFarm & "' GROUP BY Variety ORDER BY Variety")
            Do While rstVariety.EOF <> True
                sVariety = rstVariety!variety
                DoCmd.DeleteObject acQuery, "ExportTemp"
                CurrentDb.CreateQueryDef "ExportTemp", "SELECT * FROM qry_Results WHERE Farm = '" & sFarm & "' AND Variety = '" & sVariety & "'"
                DoCmd.TransferSpreadsheet acExport, , "ExportTemp", sFileName, True, sVariety
                rstVariety.MoveNext
            Loop
            Set wbk = Nothing
            Set rstVariety = Nothing
            rstFarm.MoveNext
        Loop
        Set ExcelApp = Nothing
        Set rstFarm = Nothing
    End If

  6. #6
    fastsue is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    3
    Thank you, thank you. This worked like a dream. Again thank you.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    mark it solved!

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
  •  
Other Forums: Microsoft Office Forums