Re: export to excel converts text to scientific notation

Tech-Archive recommends: Fix windows errors by optimizing your registry

I can't reproduce this here (Access 2003 SP1, WinXP SP2): when I export
a table or a simple query to Excel the system prefixes each value in a
text column with a apostrophe, which forces Excel to treat it as text
regardless of value.

Are these values coming direct from a table, or are they calculated
fields in a query you're exporting? If the latter, try including an
apostrophe in the expression, so you're exporting
'939336E90
rather than
939336E90

Another thing I'd do is to compare the settings in the registry key
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/
, looking for differences between the various machines. See

http://www.dicks-blog.com/excel/2004/06/external_data_m.html
and http://support.microsoft.com/?id=257819 for information on what
these settings do (although it's not clear - at least to me - how they
affect data types on export).

On Mon, 22 Aug 2005 14:11:01 -0700, sale10
<sale10@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>When I use the transfer spread*** action to export data from access to
>excel, the excel converts the Access's text format value '939336E90' to
>scientific notation value '9.39E+95' yet when you check the cell format in
>excel it shows it as text. This only occurs on some computers in the company
>and not on others. In addition, on the computers where it doesn't change it
>to scientific notation it doesn't keep the format of the cell consistent from
>computer to computer. On some computers it changes the format of the cell in
>excel to general format from text format, while it doesn't on some computers.
> All of the excel settings for the various computers seem to be the same.
>This problem only occurs in the records where there is one letter character
>in the string value and it is E. Does anyone know of this issue and have a
>solution? Thanks in advance.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Quantcast