Exporting to Excel - retaining number format

[Steve]

I hope someone can help with this...

I have data in Access and it displays well in all reports. The data I am
dealing with has part numbers. These part numbers often are in a format
similar to:

073D11
073E05
......

When I export a product list to excel, it is changing many (a LOT!) of the
part numbers to scientific format. Example:

073D10 becomes 730000000000 (which appears as 7.3E +11)
073E11 becomes 7300000000000 (which appears as 7.3E + 12)

How can I export o Excel and retain the proper number formats (effectively
as text)? I have searched through the Microsoft site and found it very
unhelpful I found article http://support.microsoft.com/kb/214233 and it
tells me that Excel does this. Yes, I know it does this. Under the section
"Retaining Number Formats When You Import Text Files or Use Parse", it says
the following:

"When you import a text file into Microsoft Excel or use the Text to Columns
command (located on the Data menu) to place text entries in separate
columns, Microsoft Excel applies number formats to the data if the file
contains entries similar to those mentioned in this article."

This doesn't tell me how to avoid this reformatting.

Can anyone help?

Stephen

 

[Ken Snell \(MVP\)]

In the query that is used for the data export, use a calculated field in
place of the real field, and prepend an ' character to the value:

SELECT FieldName1, FieldName2,
"'" & FieldNameWithLeadZero AS NewFieldNameWIthLeadZero
FROM TableName;

EXCEL will interpret the leading ' character to mean that the value is text.

 

[Steve]

Ken -

The report I am using now is simply a report from Access. Then I use the
Access command button "Export To Excel" to export the report to an Excel
worksheet. I am not using any SQL query to "export" the data. The SQL
Query is sed only to generate an Access report.

Is the query you are instruction me to use initiated in "generating the
access report", or "exporting the access report to Excel"? If it is the
former, then I am confused: the Access Report would display '073E11 as the
code, right? If you are referring to the latter, then I am still confused
because I am not using any SQL query to "export" the data. As mentioned
above, I am using the built-in Access command button to "Export to Excel".

Either way, I am confused. Please help me with additional explanation?

Stephen

 

[Ken Snell \(MVP\)]

I assume that you're using a query as the report's Record Source. Add the
calculated field I suggested to that query. Then try the export method for
the report. You should get that field as an additional column in the excel
export. You may or may not need to put a control on the report that is
bound to this calculated field -- quite honestly, I don't use the export to
EXCEL option because it doesn't provide me with any benefits/use, so I am
not familiar with the nuances of using this feature.

 

[Steve]

Ken -

I am going to ust add that new field to the Access report and have it print
in "white" text. This way, it is invisible on the report, but WILL appear
in the Excel output.

Thank you for your help.

Stephen

 

[Garry]

Hi Steve, have had trouble myself with this for a long while.

Have done as you suggested and the full field is exported but the '
characters appears in the excel worksheet and also prints out to paper.

What am I doing wrong

cheers, Garry