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

 

[Jamie Collins]

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.

--

 

[Jamie Collins]

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

Good to know it still works, even in Access2003.

Sorry, I thought I was posting to one of the Access groups. For Excel
users who missed my subtle point, see:

You cannot change, add, or delete data in tables that are linked to an
Excel workbook in Office Access 2003 or in Access 2002:
http://support.microsoft.com/kb/904953/en-us

"Because of legal issues**, Microsoft has disabled the functionality in
Access 2003 and in Access 2002 that let users change the data in linked
tables that point to a range in an Excel workbook."

The same applies to Jet SQL code executed anywhere from with the Access
UI (query object, VBA code, etc) that would directly update Excel data.

** For the story on the 'legal issues', see:

Lawsuit forces users to update Microsoft Office:
http://www.theregister.co.uk/2006/02/07/microsoft_office_access_infringement/

"Users of Microsoft's Office and Access packages have been told they
will have to install updates as a result of a patent infringement
dispute between Microsoft and a Guatemalan inventor that has cost the
software firm almost $9 million in damages..."

Jamie.

--