Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009

    Unanswered: Create multiple Excel files based on grouping

    I work in a hospital and am trying to create a query that would be grouped by patient number and then have all records that have that patient number be dumped into an Excel file (will create an excel file with the name of the patient #, but that's another problem altogether). I only know how to create 1 excel file with all patients and all records. So, if I have 5 patients, I would have 5 Excel files. I'm not sure if it can even be done, but any help would be appreciated.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    You don't explain which method you intend to use for exporting the data to Excel. The following example uses the DoCmd.OutputTo method. It can be modified to use another method, such as Automation for instance.

    The problem is that you need a query for each patient. This is because you cannot have Loop structures that would create a different subquery at each iteration in Access (not easily at least). Moreover, having a different query for each patient would mean that you would have to create a new query for each new patient.

    The solution I propose here uses a VBA loop to modify the WHERE part of an existing query, then export it to Excel, for each patient.

    1. Create a query (named Qry_Export_Patient in my example), like this:
    SELECT *
    FROM MyTable
    Adapt it to the table or query containing the patients data (I used Mytable) and eventually replace * by the name of the columns you want to export.

    2. Paste the following code in a new or existing independant module:
    Public Function ExportToExcel()
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim strSQLSelect As String
        Set dbs = CurrentDb
        ' Replace Qry_Export_Patient by the name of the real query.
        Set qdf = dbs.QueryDefs("Qry_Export_Patient")
        ' Replace by the proper SELECT string from the basic query.
        strSQLSelect = "SELECT * FROM MyTable"
        ' Replace the literal "SELECT..." string, using the proper names for the column and the table.
        Set rst = dbs.OpenRecordset("SELECT Patient_ID FROM MyTable", dbOpenSnapshot)
        With rst
            Do Until .EOF
                ' Replace Patient_ID by the real name of the column if necessary.
                ' Note: If Patient_ID (whatever the real name can be) is not numeric, use:
                ' qdf.SQL = strSQLSelect & " WHERE Patient_ID = '" & rst!Patient_ID & "'"
                qdf.SQL = strSQLSelect & " WHERE Patient_ID = " & rst!Patient_ID
                ' Here every Excel file is named <Patient_ID>.xls (e.g. 12345.xls),
                ' and is created in the default folder defined for Access data files. Change it if needed.
                DoCmd.OutputTo acOutputQuery, "Qry_Export_Patient", acSpreadsheetTypeExcel7, !Patient_ID & ".xls"
        End With
        Set rst = Nothing
        Set qdf = Nothing
        Set dbs = Nothing
    End Function
    Have a nice day!

Posting Permissions

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