Override worksheet name when exporting to Excel

[Guest]

When exporting a query to Excel, is there a way to override the name of the
worksheet in Excel. Excel uses the query name by default. Thanks!

 

[David Lloyd]

There are several workarounds to this issue. One is create a new QueryDef
using DAO with the name of the worksheet you desire and then set the SQL
property to SQL property of your current query.

A second approach would be to open Excel from Access using automation, and
rename the workbook. Since you already know the worksheet name and have the
file path to the workbook, this is straightforward.

A third approach, although I have not tested it, is to just temporarily
rename the query prior to export (using DAO) and then change the query name
back to its original name after exporting.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


When exporting a query to Excel, is there a way to override the name of the
worksheet in Excel. Excel uses the query name by default. Thanks!

 

[fredg]

When exporting a query to Excel, is there a way to override the name of the
worksheet in Excel. Excel uses the query name by default. Thanks!

This works for me.

Public Sub SendToExcel()
Dim strQName As String
strQName = "qryToSpreadsheet" ' The name of the stored query
Dim strNewName As String
strNewName = "The name you want to show as worksheet name"
DoCmd.Rename strNewName, acQuery, strQName

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strNewName, "Path To Excel File.xls"

DoCmd.Rename strQName, acQuery, strNewName

End Sub

 

[Guest]

Thanks Fred and David for the tip!

This works for me.

Public Sub SendToExcel()
Dim strQName As String
strQName = "qryToSpreadsheet" ' The name of the stored query
Dim strNewName As String
strNewName = "The name you want to show as worksheet name"
DoCmd.Rename strNewName, acQuery, strQName

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strNewName, "Path To Excel File.xls"

DoCmd.Rename strQName, acQuery, strNewName

End Sub