Prev: XP 10 connection limit Qs
Next: Cookies problem on Win2003 Eng + IIS 6.0
From: Kevin Humphreys on 24 Jan 2006 04:34
Hi There,

I am trying to export a recordset to an excel file using the Content Type
below in the header.

Response.ContentType = "application/vnd.ms-excel"

Which works fine however the problem is that when I have a number in a
column with a leading zero the zero gets dropped.

E.G. 01760942 will be displayed as 1760942

How can I rectify this?

Thanks,
Kevin.


From: michdoh on 24 Jan 2006 04:50
HI

This is how Excel deals with numbers. You could save this value as
text, Excel should allow you to convert this manually if you need a
numerical value. I doubt if you will be able to format the column
however putting a single quote (one of these ' ) before a numerical
value will save it as text and keep the leading zero.

Mikey

From: Kevin Humphreys on 24 Jan 2006 05:58
Hi Mikey,
I tried that. However the asp page is writing out the results into a table
and this is then exporting to the excel file.
When I use the (') before the zero the column value is displayed as
('01760942 ) instead of (01760942 ).

Anything else I can try?

Thanks,
Kevin.

<michdoh(a)yahoo.com> wrote in message
news:1138096205.081836.32580(a)o13g2000cwo.googlegroups.com...
> HI
>
> This is how Excel deals with numbers. You could save this value as
> text, Excel should allow you to convert this manually if you need a
> numerical value. I doubt if you will be able to format the column
> however putting a single quote (one of these ' ) before a numerical
> value will save it as text and keep the leading zero.
>
> Mikey
>


From: "Tom Kaminski [MVP]" <tomk mvps on 24 Jan 2006 09:06
"Kevin Humphreys" <khumphreys(a)hotmail.com> wrote in message
news:%23rqwWUNIGHA.3904(a)TK2MSFTNGP10.phx.gbl...
> Hi Mikey,
> I tried that. However the asp page is writing out the results into a table
> and this is then exporting to the excel file.
> When I use the (') before the zero the column value is displayed as
> ('01760942 ) instead of (01760942 ).
>
> Anything else I can try?

I like to use the "reverse engineering" method. Create the spreadsheet in
Excel first, formatted the way you want, then save it as HTML. Look at the
resulting HTML code to figure out what you need to do in your ASP to get the
desired results.

--
Tom Kaminski IIS MVP
http://www.microsoft.com/windowsserver2003/community/centers/iis/
http://mvp.support.microsoft.com/
http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS


From: michdoh on 24 Jan 2006 09:55
Hi kevin

I know its a total excel hack here but if you want to remove the '
after the file is imported

A simple way to do it is to create a new column and add this formula
=upper(A2) (or whichever is your starting cell).
As there is no upper balue for the ' it's removed, as this is text the
leading value is retained.
Double click the fill handle to complete the column.
Copy the column and use the edit-paste special feature to paste the
values (not the formula) of this calculation back on top of themselves.
(This leaves the string)
Delete the original column ans the spreadsheet is good to go.

NOT eligant and I'm sure you'll get a dot net solution but it would
work in the interim

Mikey

 |  Next  |  Last
Pages: 1 2
Prev: XP 10 connection limit Qs
Next: Cookies problem on Win2003 Eng + IIS 6.0