Results 1 to 4 of 4
  1. #1
    WhosUsingMyName is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    2

    Access Query - Export to multiple Excel files

    I have a basic access query (MACs Report Template) that I need to export into multiple excel files based on the 1st field (Plan ID).



    Example:

    PLAN ID Number Amount Status
    AM141 12345 100 Disconnected
    AM141 54321 5000 Active
    AM142 11122 2000 Disconnected
    AM155 22334 500 Disconnected










    I need this to create a spreadsheet for each unique PLAN ID. Ideally i want it to export the following:

    MACs Report AM141 20150521.xls (both records above should be in this report)
    MACs Report AM142 20150521.xls
    MACs Report AM155 20150521.xls

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    Looping structure code in VBA can accomplish. Review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    WhosUsingMyName is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    2
    I think the second section of what you sent is what I need to do, but i am a complete beginner and do not know how to modify this code to work in my database. Can you explain in more detail?

    Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)
    Generic code to create a temporary query, get list of filtering values, and then loop through the list to filter various data and export each filtered query to separate EXCEL files. In this sample code, the employees assigned to each manager are exported to separate EXCEL files, one file for each manager.
    Dim qdf As DAO.QueryDef
    Dim dbs As DAO.Database
    Dim rstMgr As DAO.Recordset
    Dim strSQL As String, strTemp As String, strMgr As String

    Const strQName As String = "zExportQuery"

    Set dbs = CurrentDb

    ' Create temporary query that will be used for exporting data;
    ' we give it a dummy SQL statement initially (this name will
    ' be changed by the code to conform to each manager's identification)

    strTemp = dbs.TableDefs(0).Name
    strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
    Set qdf = dbs.CreateQueryDef(strQName, strSQL)
    qdf.Close
    strTemp = strQName

    ' *** code to set strSQL needs to be changed to conform to your
    ' *** database design -- ManagerID and EmployeesTable need to
    ' *** be changed to your table and field names
    ' Get list of ManagerID values -- note: replace my generic table and field names
    ' with the real names of the EmployeesTable table and the ManagerID field

    strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
    Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

    ' Now loop through list of ManagerID values and create a query for each ManagerID
    ' so that the data can be exported -- the code assumes that the actual names
    ' of the managers are in a lookup table -- again, replace generic names with
    ' real names of tables and fields
    If rstMgr.EOF = False And rstMgr.BOF = False Then
    rstMgr.MoveFirst
    Do While rstMgr.EOF = False
    ' *** code to set strMgr needs to be changed to conform to your
    ' *** database design -- ManagerNameField, ManagersTable, and
    ' *** ManagerID need to be changed to your table and field names
    ' *** be changed to your table and field names

    strMgr = DLookup("ManagerNameField", "ManagersTable", _
    "ManagerID = " & rstMgr!ManagerID.Value)
    ' *** code to set strSQL needs to be changed to conform to your
    ' *** database design -- ManagerID and EmployeesTable need to
    ' *** be changed to your table and field names

    strSQL = "SELECT * FROM EmployeesTable WHERE " & _
    "ManagerID = " & rstMgr!ManagerID.Value & ";"
    Set qdf = dbs.QueryDefs(strTemp)
    qdf.Name = "q_" & strMgr
    strTemp = qdf.Name
    qdf.SQL = strSQL
    qdf.Close
    Set qdf = Nothing
    ' Replace C:\FolderName\ with actual path
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
    "ddMMMyyyy_hhnn") & ".xls"
    rstMgr.MoveNext
    Loop
    End If

    rstMgr.Close
    Set rstMgr = Nothing

    dbs.QueryDefs.Delete strTemp
    dbs.Close
    Set dbs = Nothing

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    Follow the green text instructions.

    Instead of ManagerID you want PlanID and instead of EmployeesTable you want your table or query (MACs Report Template).

    Change variable names as appropriate to be more meaningful in your db, such as strMgr could be strPlan.

    Adapt or remove other code as appropriate. You won't need the DLookup for manager name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Export one query to multiple Excel files
    By bliffer in forum Import/Export Data
    Replies: 3
    Last Post: 01-29-2014, 02:37 PM
  2. Replies: 3
    Last Post: 01-07-2014, 03:39 PM
  3. Replies: 11
    Last Post: 12-20-2012, 12:30 PM
  4. Export Access into multiple excel files based on field value
    By turntabl1st in forum Import/Export Data
    Replies: 7
    Last Post: 11-08-2012, 12:43 PM
  5. Export Query to Various Excel Files
    By dalet in forum Import/Export Data
    Replies: 7
    Last Post: 11-09-2009, 09:22 AM

Tags for this Thread

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