ExcelBanter - View Single Post

Jamie Collins

wrote:

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

Good to know it still works, even in Access2003.

FWIW someone (me?) seems to have fudged the code slightly: the argument
strWorksheetName is unused. I think the appropriate line of code should
be more like this:

strSQLToExecute = _
"SELECT [" & strItemToOutput & _
"].* INTO [Excel 8.0;Database=" & strOutputPathAndFile & _
"].[" & strWorksheetName & "] FROM " & strItemToOutput

Note the argument name strWorksheetName could be slightly misleading.

The SELECT..INTO..FROM syntax will create a new Excel Defined Name
('named range') with a name (little n) to match the value of
strWorksheetName, unless a Defined Name already exists of the same name
or is otherwise an invalid name for a Name (e.g. illegal characters,
quotes not escaped, etc), in which case the operation will fail with an
error.

The new Defined Name will be created on a new worksheet, also named to
match strWorksheetName, unless a worksheet already exists of the same
name, in which case its name will be 'uniquified', usually by appending
the name with '1' e.g. if strWorksheetName = "Sheet1" and there is
already a worksheet named "Sheet1" the new sheet will be named
"Sheet11".

So the resulting worksheet name may not be the same as
strWorksheetName.

Jamie.

--