M
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
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