Retaining formatting when exporting data from Access to Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When converting an access table to excel it loses it's formatting and
rearranges the columns in alpha order. How can this be prevented?
 
Gilda said:
When converting an access table to excel it loses it's formatting and
rearranges the columns in alpha order. How can this be prevented?
Click to expand...

Formatting is simply a display attribute that does nothing to the underlying
raw data and it is the raw data that is exported.

Build a query that uses the Format() function (not the property) to produce
the output you want and then export the query instead of the table.
 
is there anywhere I can find info on how to do this? I understand that the
raw data remains however on a large amount of data it takes a considerable
amount of time to reformat to the format that existed in Access. Thank you
for your time and patience with this.
 
Gilda said:
is there anywhere I can find info on how to do this? I understand
that the raw data remains however on a large amount of data it takes
a considerable amount of time to reformat to the format that existed
in Access. Thank you for your time and patience with this.

"Rick Brandt" wrote:
Click to expand...

Quick explanation for one field...

New query with your single table as input. To set up a date field in the grid
(we'll say the name of this field is [EntryDate]). In the first cell have...

EntryDate: Format([TableName]![EntryDate], "yyyy-mm-dd")

When you preview the datasheet of the query you will see your date field output
with the entered format. If you were to export the query that format should be
retained. Repeat as necessary for all other fields in your table.
 
Hello Rick

is there a way to cause access to format the sheet in different ways? for
instance could it add in the data at a certain cell on the spreadsheet, add
titles or other stuff? or would it be better to use excel to import external
data?

Rick Brandt said:
Gilda said:
is there anywhere I can find info on how to do this? I understand
that the raw data remains however on a large amount of data it takes
a considerable amount of time to reformat to the format that existed
in Access. Thank you for your time and patience with this.

"Rick Brandt" wrote:
Click to expand...

Quick explanation for one field...

New query with your single table as input. To set up a date field in the grid
(we'll say the name of this field is [EntryDate]). In the first cell have...

EntryDate: Format([TableName]![EntryDate], "yyyy-mm-dd")

When you preview the datasheet of the query you will see your date field output
with the entered format. If you were to export the query that format should be
retained. Repeat as necessary for all other fields in your table.
Click to expand...
 
Back
Top