How to Specify Worksheet Name when Exporting to Excel

[MIG]

When I export an Access query to Excel, the name of the query is too
long for the Excel worksheet name, so I get an error telling me that
the name is invalid and the data does not export.

However, there seems to be no opportunity to specify the name of the
worksheet that will be created. One can specify the Excel filename,
but it's the original query name that seems to determine the worksheet
name.

Does anyone know a way round this (other than messing up the query
naming conventions before exporting)?

Thanks.

 

[Klatuu]

The worksheet name is specified in the Range argument of the
TransferSpreadsheet.

 

[MIG]

The worksheet name is specified in the Range argument of the
TransferSpreadsheet.
--
Dave Hargis, Microsoft Access MVP








- Show quoted text -

Ah right. Is there also a way of accessing this when exporting
manually?

In fact, I am now wondering if the problem was something else. It may
have been that Access was objecting to one of the field names (query
name dot etc etc that built the final query, hence long), but if so I
don't know why it would object when exporting but not when saving or
running the query.

 

[Jerry Whittle]

You could export it using a Macro with Transfer Spreadsheet. That allows you
to pick a name for the Excel file.

Or you could do it in code either in a module, like below, or from a command
button on a form.

Function Output2Excel()
On Error GoTo Output2Excel_Err

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, 8, "QueryName", "q:\abc.xls", False,
""
DoCmd.SetWarnings True

Output2Excel_Exit:
Exit Function

Output2Excel_Err:
MsgBox Error$
Resume Output2Excel_Exit

End Function

 

[MIG]

You could export it using a Macro with Transfer Spreadsheet. That allows you
to pick a name for the Excel file.

Or you could do it in code either in a module, like below, or from a command
button on a form.

Function Output2Excel()
On Error GoTo Output2Excel_Err

    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acExport, 8, "QueryName", "q:\abc.xls",False,
""
    DoCmd.SetWarnings True

Output2Excel_Exit:
    Exit Function

Output2Excel_Err:
    MsgBox Error$
    Resume Output2Excel_Exit

End Function

I realise that this and the other answer actually refer to the
(Windows) filename.

The thing that I can't seem to specify is the worksheet name, which
automatically comes up as the table/query name, regardless of the
filename I specify.

This can be changed manually in Excel of course, but I wonder if there
is an alternative.

 

[aaron_kempf]

uh.. have a wrapper query with a shorter name.. instead of renaming
everything?

 

[MIG]

uh.. have a wrapper query with a shorter name.. instead of renaming
everything?

Sorry, didn't check back on this correspondence. I spose this could
be automated somehow ...

Will look into it. Tar.

 

[RTHSC]

I have a button with the following code with which I have the query create a
table, then export it to Excel.

stDocName = "qryCreateTblMonthlyBilling"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OutputTo acTable, "monthlyReport", "MicrosoftExcel(*.xls)",
"monthlyJoblogReport.xls", True, ""

I find this works well - it overwrites the table each time and then
overwrites the Excel worksheet.

I have a question - in Access 97, I used to be able to add the current date
to the filename. I would key
DoCmd.OutputTo acTable, "HSCmonthlyReport", "MicrosoftExcel(*.xls)",
Date & "HSCmonthlyJoblogReport.xls", True, "" and it would insert the current
date at the beginning of the worksheet name. Now, using 2000 it doesn't work
so I have to rename the worksheet in Excel each time I run it so it doesn't
get overwritten the next time - not a huge problem, but an extra step. ANY
IDEAS?