Prev: Charting the Sum of Two, or More, Series of Data
Next: Solver in vba
From: MikeL on 20 Apr 2010 13:56
Here's my dilemma and challenge…

… This is through Automation…

I'm receiving an XML file that has excel formatting…

It's very easy to import, the problem comes in when I import large numbers
(17 – 20 characters long)… They should be imported as text, but they are
importing as numbers, so they appear as scientific notation… When I go to
create a file from the data, the numbers are being written in the scientific
notation and not as text…

I am unable to control the content of the XML file. I do know the number is
in a certain column…

Also, when I double click on a cell, I can edit it and place a single quote
and it will store the number correctly… But, when I do it through automation,
it won't convert…

We are using excel 2003… In the process of upgrading to 2007, but won't be
for another 6 months…

Fustrating...

Your help would be greatly appreciated..

Thanks,
Michael

From: Gary Brown on 20 Apr 2010 15:04
1st of all, only the first 15 digits will convert. The rest will be '0's.
Example, importing 12345678901234567890 to Excel will convert to
12345678901234500000.

To convert an entire column of numbers to text quickly, you can use
something like...
Selection.TextToColumns _
Destination:=Selection.Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, xlTextFormat), _
TrailingMinusNumbers:=True

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"MikeL" wrote:

> Here's my dilemma and challenge…
>
> … This is through Automation…
>
> I'm receiving an XML file that has excel formatting…
>
> It's very easy to import, the problem comes in when I import large numbers
> (17 – 20 characters long)… They should be imported as text, but they are
> importing as numbers, so they appear as scientific notation… When I go to
> create a file from the data, the numbers are being written in the scientific
> notation and not as text…
>
> I am unable to control the content of the XML file. I do know the number is
> in a certain column…
>
> Also, when I double click on a cell, I can edit it and place a single quote
> and it will store the number correctly… But, when I do it through automation,
> it won't convert…
>
> We are using excel 2003… In the process of upgrading to 2007, but won't be
> for another 6 months…
>
> Fustrating...
>
> Your help would be greatly appreciated..
>
> Thanks,
> Michael
>
From: Dave Peterson on 20 Apr 2010 16:09
I'm not sure how you're importing that XML file, but if you want to preserve all
17 digits, maybe you can bring the data in as text and then clean up the stuff
that doesn't belong.



MikeL wrote:
>
> Here's my dilemma and challenge…
>
> … This is through Automation…
>
> I'm receiving an XML file that has excel formatting…
>
> It's very easy to import, the problem comes in when I import large numbers
> (17 – 20 characters long)… They should be imported as text, but they are
> importing as numbers, so they appear as scientific notation… When I go to
> create a file from the data, the numbers are being written in the scientific
> notation and not as text…
>
> I am unable to control the content of the XML file. I do know the number is
> in a certain column…
>
> Also, when I double click on a cell, I can edit it and place a single quote
> and it will store the number correctly… But, when I do it through automation,
> it won't convert…
>
> We are using excel 2003… In the process of upgrading to 2007, but won't be
> for another 6 months…
>
> Fustrating...
>
> Your help would be greatly appreciated..
>
> Thanks,
> Michael

--

Dave Peterson
 | 
Pages: 1
Prev: Charting the Sum of Two, or More, Series of Data
Next: Solver in vba