Q:

Exporting data from Access to Excel workbooks/sheets based on numerous conditions

I have some data structured as follows:
sglAccNumber    intDaysOld      intRouterLocation   intDaysInLocation
1638828663      614             Customer Service    05. - 61-90 Days
1955963013      348             Advertising         03. 16-45 Days
1198680816      1678            Accounting          09. 401-730 Days
1892708307      1860            Accounting          010. 730+ Days
1785581943      1005            Asset Management    02. 6-15 Days
1942406908      1853            Finances            09. 401-730 Days

etc... There are 60,000 rows of data.
I am looking to move the data from the Access table to numerous different workbooks, based on the intRouterLocation name. The bit I'm struggling with is, within each individual workbook, also to move the data to a worksheet with the name intDaysInLocation.
For example, using the data above, the Accounting workbook would have two sheets generated, one for 09. 401-730 Days, and one for 010. 730+ Days and the appropriate entries would populate each. 
I have been struggling with this for the past few days and can get the data to go into either the workbooks by name, or the intDaysInLocation by value, but combining them both has gotten the better of me.  
Is this something that would be possible using VBA?
The code I'm using to collate the sheets only (all in one sheet, the intRouterLocation isn't taken into account):
Sub exportMk2 ()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strPath As String
Dim strSelectOneType As String
Dim strSelectDaysInLocation As String

' (change strPath back to what you need)
strPath = CurrentProject.Path & Chr(92) & "Pets_dataset_export_" & _
    Format(Date, "yyyy-mm-dd") & ".xlsx"
strSelectDaysInLocation = "SELECT DISTINCT p.intDaysInLocation" & vbCrLf & _
    "FROM Worksheet AS p;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSelectDaysInLocation, dbOpenSnapshot)
Set rsRouters = db.OpenRecordset(strSelectDaysInLocation, dbOpenSnapshot)

For Each routerLocation In rsRouters
    Do While Not rs.EOF
        strSelectOneType = "SELECT p.ID, p.intDaysInLocation, p.intRouterLocation" & vbCrLf & _
            "FROM Worksheet AS p" & vbCrLf & _
            "WHERE p.intDaysInLocation='" & rs!intDaysInLocation.Value & "';"
        Debug.Print strSelectOneType
        Set qdf = db.QueryDefs("qryExportMe")
        qdf.SQL = strSelectOneType
        qdf.Close
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
            "qryExportMe", strPath, True, "woot " & rs!intDaysInLocation.Value
        rs.MoveNext
    Loop
Next
rs.Close
End Sub

A:

I think I built what you need. Just point it towards the right table, fields and export location as in the Test sub. It needs to be run from Access, with a reference to your Excel library.
Public Sub Test()

    ExportToExcel "tblData", "intRouterLocation", "intDaysInLocation", CurrentProject.Path & "\Export\"

End Sub

Public Sub ExportToExcel(sTableName As String, sWorkBookNameField As String, sSheetNameField As String, sDestinationFolder As String)

    Dim rsData          As Recordset
    Dim oXL             As Excel.Application
    Dim oWB             As Excel.Workbook
    Dim oSH             As Excel.Worksheet
    Dim sPrevWB         As String
    Dim sPrevSheet      As String
    Dim lRecordcount    As String
    Dim vTempArray()    As Variant
    Dim lFieldID        As Long
    Dim lRecordID       As Long

    With CurrentDb.OpenRecordset("SELECT [" & sWorkBookNameField & "],[" & sSheetNameField & "] FROM [" & sTableName & "] GROUP BY [" & sWorkBookNameField & "],[" & sSheetNameField & "] ORDER BY [" & sWorkBookNameField & "],[" & sSheetNameField & "] DESC;")
        If .EOF And .BOF Then
            .Close
            MsgBox "No data found"
            Exit Sub
        End If

        Set oXL = New Excel.Application

        Do Until .EOF
            If sPrevWB <> .Fields(sWorkBookNameField) Then
                If Not oWB Is Nothing Then
                    oWB.Close True
                    Set oWB = oXL.Workbooks.Add
                Else
                    With oXL
                        Set oWB = .Workbooks.Add
                        .Calculation = xlCalculationManual
                        .ScreenUpdating = False
                    End With
                End If

                oWB.SaveAs sDestinationFolder & .Fields(sWorkBookNameField) & ".xlsx"
                sPrevWB = .Fields(sWorkBookNameField)
                Set oSH = oWB.Sheets(1)
            ElseIf sPrevSheet <> .Fields(sSheetNameField) Then
                If oSH.Index + 1 > oWB.Sheets.Count Then oWB.Sheets.Add
                Set oSH = oWB.Sheets(oSH.Index + 1)
            End If

            oSH.Name = .Fields(sSheetNameField)

            'Push data to sheet (numerous methods, I just picked one)
            Set rsData = CurrentDb.OpenRecordset("SELECT * FROM [" & sTableName & "] WHERE [" & sWorkBookNameField & "]='" & .Fields(sWorkBookNameField) & "' AND [" & sSheetNameField & "]='" & .Fields(sSheetNameField) & "'")

            rsData.MoveLast
            lRecordcount = rsData.RecordCount
            rsData.MoveFirst

            vTempArray = rsData.GetRows(lRecordcount)

            For lFieldID = 0 To UBound(vTempArray, 1)
                oSH.Cells(1, lFieldID + 1) = rsData.Fields(lFieldID).Name
                For lRecordID = 0 To UBound(vTempArray, 2)
                    oSH.Cells(lRecordID + 2, lFieldID + 1) = vTempArray(lFieldID, lRecordID)
                Next lRecordID
            Next lFieldID
            oSH.Cells.EntireColumn.AutoFit
            .MoveNext
        Loop
        .Close
    End With

    oWB.Save
    oXL.Quit

    Set rsData = Nothing
    Set oSH = Nothing
    Set oWB = Nothing
    Set oXL = Nothing

End Sub

