Database Export to Excel
 

We have a Database program here (SQL not Access) that
exports reports to Excel. These reports contain odd
combinations of numbers that mean something to the users
of the database and to the programmers of it, but not to
me. Anyway, I recently upgraded from Office 2000 on our
terminal server (Win2K) to Office XP. Since then the
num,bers in this worksheet are being truncated and the
formats are changing. For instance, a number such as
2E152 appears as 2.00E+152 or something like 0100a will
appear as 1:00 AM, we are also having the leading 0's for
a number deleted. I know how I can change the format for
each cell individually, but how can I change the
formatting for cells in the entire spreadhseet to read the
munbers as text only. Or some other fix would be great.
I have Office XP on a nother server and this export isn't
goofed like this. Don't understand it and have too much
to do to spend more time on it so any help would be
awesome! Thanks!

Database Export to Excel
 

It would depend on what you mean by export. If it is exported as a text
file (but not .csv), then imported, you can import it manually through
file=Open which will invoke the text import wizard. On the last dialog of
the wizard, you can specify to treat specific columns as text and no
conversion will take place. Do this with the macro recorder turned on to
get the code to import it without going through the text import wizard, but
still use the settings you specified.

Other than that, I think you would have to elaborate on the format of the
file that is exported.

Excel is seeing 2E152 as being a number in scientific or exponential
notation. This is pretty standard, so I am not sure why earlier versions
were not having a similar problem. Same with 0100a being seen as a time
value. Leading zeros are lost when the value is stored as a number -
again, this is pretty common in all versions as well. For some reason, the
machines without the problems must have treated these columns as Text.

--
Regards,
Tom Ogilvy


"donna" wrote in message
...
We have a Database program here (SQL not Access) that
exports reports to Excel. These reports contain odd
combinations of numbers that mean something to the users
of the database and to the programmers of it, but not to
me. Anyway, I recently upgraded from Office 2000 on our
terminal server (Win2K) to Office XP. Since then the
num,bers in this worksheet are being truncated and the
formats are changing. For instance, a number such as
2E152 appears as 2.00E+152 or something like 0100a will
appear as 1:00 AM, we are also having the leading 0's for
a number deleted. I know how I can change the format for
each cell individually, but how can I change the
formatting for cells in the entire spreadhseet to read the
munbers as text only. Or some other fix would be great.
I have Office XP on a nother server and this export isn't
goofed like this. Don't understand it and have too much
to do to spend more time on it so any help would be
awesome! Thanks!

Database Export to Excel
 

The export is happening inside a VB6.0 Application.

The excel object is opened and a worksheet created.

We fill the cells using the .copyfromrecordset method of
the cell object within the excel object.

The load point for Office XP for the working system is
different from the load point for the non-working system.
Maybe some pre install features were configured
differently, not sure. I was just wondering if there may
be some registry changes I could make so the default cell
category is text.


-----Original Message-----
It would depend on what you mean by export. If it is
exported as a text
file (but not .csv), then imported, you can import it
manually through
file=Open which will invoke the text import wizard. On
the last dialog of
the wizard, you can specify to treat specific columns as
text and no
conversion will take place. Do this with the macro
recorder turned on to
get the code to import it without going through the text
import wizard, but
still use the settings you specified.

Other than that, I think you would have to elaborate on
the format of the
file that is exported.

Excel is seeing 2E152 as being a number in scientific or
exponential
notation. This is pretty standard, so I am not sure why
earlier versions
were not having a similar problem. Same with 0100a
being seen as a time
value. Leading zeros are lost when the value is stored
as a number -
again, this is pretty common in all versions as well.
For some reason, the
machines without the problems must have treated these
columns as Text.

--
Regards,
Tom Ogilvy


"donna" wrote in
message
...
We have a Database program here (SQL not Access) that
exports reports to Excel. These reports contain odd
combinations of numbers that mean something to the users
of the database and to the programmers of it, but not to
me. Anyway, I recently upgraded from Office 2000 on our
terminal server (Win2K) to Office XP. Since then the
num,bers in this worksheet are being truncated and the
formats are changing. For instance, a number such as
2E152 appears as 2.00E+152 or something like 0100a will
appear as 1:00 AM, we are also having the leading 0's
for
a number deleted. I know how I can change the format
for
each cell individually, but how can I change the
formatting for cells in the entire spreadhseet to read
the
munbers as text only. Or some other fix would be great.
I have Office XP on a nother server and this export
isn't
goofed like this. Don't understand it and have too much
to do to spend more time on it so any help would be
awesome! Thanks!


.

Database Export to Excel
 

You can change the normal style.

This is a workbook level setting

Format=Styles, Normal in the dropdown, click the modify button, select Text
in the number tab.



--
Regards,
Tom Ogilvy

wrote in message
...
The export is happening inside a VB6.0 Application.

The excel object is opened and a worksheet created.

We fill the cells using the .copyfromrecordset method of
the cell object within the excel object.

The load point for Office XP for the working system is
different from the load point for the non-working system.
Maybe some pre install features were configured
differently, not sure. I was just wondering if there may
be some registry changes I could make so the default cell
category is text.


-----Original Message-----
It would depend on what you mean by export. If it is
exported as a text
file (but not .csv), then imported, you can import it
manually through
file=Open which will invoke the text import wizard. On
the last dialog of
the wizard, you can specify to treat specific columns as
text and no
conversion will take place. Do this with the macro
recorder turned on to
get the code to import it without going through the text
import wizard, but
still use the settings you specified.

Other than that, I think you would have to elaborate on
the format of the
file that is exported.

Excel is seeing 2E152 as being a number in scientific or
exponential
notation. This is pretty standard, so I am not sure why
earlier versions
were not having a similar problem. Same with 0100a
being seen as a time
value. Leading zeros are lost when the value is stored
as a number -
again, this is pretty common in all versions as well.
For some reason, the
machines without the problems must have treated these
columns as Text.

--
Regards,
Tom Ogilvy


"donna" wrote in
message
...
We have a Database program here (SQL not Access) that
exports reports to Excel. These reports contain odd
combinations of numbers that mean something to the users
of the database and to the programmers of it, but not to
me. Anyway, I recently upgraded from Office 2000 on our
terminal server (Win2K) to Office XP. Since then the
num,bers in this worksheet are being truncated and the
formats are changing. For instance, a number such as
2E152 appears as 2.00E+152 or something like 0100a will
appear as 1:00 AM, we are also having the leading 0's
for
a number deleted. I know how I can change the format
for
each cell individually, but how can I change the
formatting for cells in the entire spreadhseet to read
the
munbers as text only. Or some other fix would be great.
I have Office XP on a nother server and this export
isn't
goofed like this. Don't understand it and have too much
to do to spend more time on it so any help would be
awesome! Thanks!


.