Export to Excel causes field name issue

[Guest]

I'm exporting two Access tables (that were populated by doing several append
queries) into an existing excel workbook. The export is based upon a click
event of a form and I perform this export to the exact same excel worksheet
in the exact same excel workbook once a week. Before I export, I clear the
old records from the excel worksheet but retain the worksheet and its name.
The excel worksheet is the source data for several pivot tables within this
same workbook. All the pivot tables have the dynamic range link to the
worksheet to pick up changes in records or columns etc. So here's my
problem. When I do this export the field names from my table are
exported...but I want the caption name that I set up at the table level to be
the field name that exports. The odd thing is that when I'm in the table
datasheet and I click the excel icon from the toolbar, I get the caption
names copied to an excel worksheet. Is there any way to insure that the
caption names appear when exporting using a click event from a form. Thank
you so much.

 

[Ken Snell \(MVP\)]

Use a query to collect the data you wish to export from the tables, and use
an Alias for field names in the query. Then export the query.

Example:

SELECT Field1 AS MyField1, Field2 AS MyField2
FROM TableName;

Use the caption names as the alias names (in place of MyField1, MyField2,
etc.).