Naming Worksheet Tab on Export

[Leesha]

Hi,
I'm export a query to excel and would like to specify what the worksheet tab name should vs having it name as the query name. Is this possible?
Thanks,
Leesha

[HansV]

Access always uses the name of the object that you export as sheet name.
One workaround would be to open the workbook after the export, rename the sheet, then save and close the workbook.
Another one would be to rename the query temporarily to the name you want for the sheet, then restore the original name.
Here is code for the second option:

Code: Select all

Sub ExportQuery(QueryName As String, FileName As String, SheetName As String)
    DoCmd.Rename _
        NewName:=SheetName, _
        ObjectType:=acQuery, _
        OldName:=QueryName
    DoCmd.TransferSpreadsheet _
        TransferType:=acExport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:=SheetName, _
        FileName:=FileName, _
        HasFieldNames:=True
    DoCmd.Rename _
        NewName:=QueryName, _
        ObjectType:=acQuery, _
        OldName:=SheetName
End Sub

Use it like this:

Code: Select all

Sub Demo()
    ExportQuery _
        QueryName:="qrySalesOhio", _
        FileName:="C:\Excel\Demo.xlsx", _
        SheetName:="Sales Data for Ohio"
End Sub

[Leesha]

Thanks Hans!!