Output an Access Table or Query to Excel

[millardgroups]

I thought I would post this subroutine for outputting tables or queries
to Excel. My thanks for Jamie Collins for his response to a 2005 post.
He showed an easy way to do this without having to use Excel objects.
The instructions point out why this is an alternative to the docmd
method.

'---------------------------------------------------------------------------------------
' Procedure : subDumpToExcel
' DateTime : 11/22/2006 11:03
' Author : ws
' Copyright: Public Domain
' Purpose : This subroutine will export a table or query to Excel
' This offers more control than is possible with the
DoCmd.OutputTo method
' DoCmd.OutputTo creates a new Excel file for each output
(or overwrites an existing file)
' This option will add a new worksheet for the data
output, if a worksheet with the name does not exist
' Inputs : Call the routine as subDumpToExcel [Query or Table to
Output], [Output Path and File Name], [Worksheet Name]
'---------------------------------------------------------------------------------------
'
Sub subDumpToExcel(strItemToOutput As String, strOutputPathAndFile As
String, strWorksheetName As String)

Dim strSQLToExecute As String

'On Error GoTo subDumpToExcel_Error

strSQLToExecute = "SELECT [" & strItemToOutput & "].* INTO [Excel
8.0;Database=" & strOutputPathAndFile & "].[" & strItemToOutput & "]
FROM " & strItemToOutput
Debug.Print "strSQLToExecute is " & strSQLToExecute
CurrentDb.Execute strSQLToExecute

On Error GoTo 0
Exit Sub

subDumpToExcel_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in the
subDumpToExcel subroutine"

End Sub