Export A Dynamic Query TO Excel

DoCmd.OutputTo acOutputTable, "YourTable", acFormatXLS, "C:\ExcelFileName.xls"

Sub AdHocToExcel(SQLString As String, WorkbookName As String, IncludeFieldNames As Boolean, AutoStart As Boolean, Optional WorksheetName As String)
On Error Resume Next
Dim appExcel As Object, objWorkbook As Object, objWorksheet As Object
Dim dbsCurrent As DAO.Database
Dim rstOutput As DAO.Recordset
Dim blnSpawnedExcel As Boolean
Dim lngRow As Long, lngColumn As Long
Dim strWorkbookPathName As String

Set dbsCurrent = CurrentDb
Set rstOutput = dbsCurrent.OpenRecordset(SQLString)
If rstOutput.RecordCount = 0 Then
  GoTo Clean_up
End If

'Check if Excel is open and grab or create
Set appExcel = GetObject(, "Excel.Application")
If Err.Number = 429 Then
  Set appExcel = CreateObject("Excel.Application")
  blnSpawnedExcel = True
  Err.Clear
ElseIf Err.Number <> 0 Then
  Err.Raise Err.Number, Err.Description
  GoTo Clean_up
End If

Set objWorkbook = appExcel.Workbooks.Add
Set objWorksheet = objWorkbook.worksheets.Add
'So the user does not see the data dumping to the workbook
objWorksheet.Visible = False
If IsNull(WorksheetName) Then
  objWorksheet.Name = "Ad Hoc"
Else
  objWorksheet.Name = WorksheetName
End If

'Write the data by cycling through each record, and each column
Do
  lngRow = lngRow + 1
  For lngColumn = 0 To rstOutput.Fields.Count - 1
    If IncludeFieldNames And lngRow = 1 Then
      objWorksheet.Cells(lngRow, lngColumn + 1) = rstOutput.Fields(lngColumn).Name
    Else
      objWorksheet.Cells(lngRow, lngColumn + 1) = rstOutput.Fields(lngColumn)
    End If
  Next lngColumn
  rstOutput.MoveNext
Loop Until rstOutput.EOF

objWorkbook.SaveAs WorkbookName
If AutoStart Then
  objWorksheet.Visible = True
  objWorksheet.Activate
  blnSpawned = False
Else
  objWorkbook.Close
End If

Set objWorksheet = Nothing
Set objWorkbook = Nothing
If blnSpawnedExcel Then
  appExcel.Quit
End If

Clean_up:
rstOutput.Close
Set rstOutput = Nothing
Set dbsCurrent = Nothing
End Sub

Table 1 Example Data
--------------------
DID   dlName
--------------------
01    .dl employees
02    .dl contractors
03    .dl Building 02
04    .dl Corporate
05    .dl HR

Table 2 Example Data
-------------------------------------
ID   FName          LName         DID
-------------------------------------
01   Gracy Lu       Freebush      02
02   John           Doe           01
03   Bob            Richard       02 
04   Jimmy          Johns         02
05   Jane           Doe           01 
06   Ricky          Bobby         02 
07   Sub            Way           02  
08   Gracy Lu       Freebush      04
09   John           Doe           05
10   Bob            Richard       03

Sample Export of Data (for excel file dl_contractors.xls)
-------------------------------------
ID   FName          LName         DID
-------------------------------------
01   Gracy Lu       Freebush      02
03   Bob            Richard       02 
04   Jimmy          Johns         02
06   Ricky          Bobby         02 
07   Sub            Way           02