Board index » delphi » After Import to Excel, it doesn't recognize numeric info

After Import to Excel, it doesn't recognize numeric info

Hi,

     I am parsing a comma delimited file and filling in the cells in Excel -
some of the fields are text and some are numeric, but Excel won't recognize
the numeric values as numbers, even if I set the Column Format to numeric
from within Excel.  I know this is happening, because doing a SUM on the
numeric column results in 0, but if I just type over the imported numeric
value with the same number, it works.  I am connecting to Excel using early
binding and loading the cells a row at a time:

 ws.Range[GetExcelCellName(1, RowNumber),
                 GetExcelCellName(TotalNumberOfColumns,
                                               RowNumber)].Value :=
VariantArray;

where VariantArray is an OLEVariant and is filled in with a rows worth of
values.

Any ideas?  Also, is there a way that I can just use the Excel Import
Manager directly - this may solve the problem, too.

Thank you very much,

Mike Thrapp
Software Consulting Associates

 

Re:After Import to Excel, it doesn't recognize numeric info


Hi,

     Some more information on this problem - the numbers are coming across
as string because I created the VariantArray as data type varOLEStr.
However, even when I change the format of the column to numeric once it is
Excel, it still thinks the items are strings.  What I am going to do for now
is insert cell by cell figuring out the data type of each data item before
it goes into Excel and using a different variant data type in the
VariantArray depending on what it is.  Also, I am trying to get the
TextToColumns method to work, since this may solve the problem, but I
haven't gotten it to work yet - if anybody knows anything more about this
method or has a better idea on my main problem, I would greatly appreciate
you help.

Thanks,

Mike Thrapp

Quote
"Mike Thrapp" <mi...@sca-corp.com> wrote in message

news:3cd9878e$1_2@dnews...
Quote
> Hi,

>      I am parsing a comma delimited file and filling in the cells in
Excel -
> some of the fields are text and some are numeric, but Excel won't
recognize
> the numeric values as numbers, even if I set the Column Format to numeric
> from within Excel.  I know this is happening, because doing a SUM on the
> numeric column results in 0, but if I just type over the imported numeric
> value with the same number, it works.  I am connecting to Excel using
early
> binding and loading the cells a row at a time:

>  ws.Range[GetExcelCellName(1, RowNumber),
>                  GetExcelCellName(TotalNumberOfColumns,
>                                                RowNumber)].Value :=
> VariantArray;

> where VariantArray is an OLEVariant and is filled in with a rows worth of
> values.

> Any ideas?  Also, is there a way that I can just use the Excel Import
> Manager directly - this may solve the problem, too.

> Thank you very much,

> Mike Thrapp
> Software Consulting Associates

Re:After Import to Excel, it doesn't recognize numeric info


Quote
In article <3cda87f3$1_1@dnews>, Mike Thrapp wrote:
> I would greatly appreciate
> you help.

How about prepending = to the numeric ones?

Jim
--
Thu, 09 May 2002 16:29 EDT
Jim O'Brien, UnitOOPS Software

Re:After Import to Excel, it doesn't recognize numeric info


Jim,

     Unfortunately, Excel did not strip the equal signs and treat them as
numeric.  However, I did figure out how to drive the Excel Text file Import
wizard so that Excel takes care of the formatting.  Here is an example:

        Wbk := ExcelApplication.Workbooks.Open(TempFileName,
                                               0,  {Don't update links}
                                               False,  {Not read only}
                                               2,  {Comma delimited}
                                               EmptyParam,  {Password}
                                               EmptyParam, {Write pswd}
                                               True,  {Ignore read only
msgs}
                                               EmptyParam, {Origin}
                                               EmptyParam, {Delimiter}
                                               EmptyParam, {Editable}
                                               EmptyParam, {Notify}
                                               EmptyParam, {Converter}
                                               True, {AddToMru}
                                               lcID);

Mike Thrapp

Quote
"Jim O'Brien" <unito...@remove-this-prefix.unitoops.com> wrote in message

news:VA.0000031e.3828a6df@remove-this-prefix.unitoops.com...
Quote
> In article <3cda87f3$1_1@dnews>, Mike Thrapp wrote:
> > I would greatly appreciate
> > you help.

> How about prepending = to the numeric ones?

> Jim
> --
> Thu, 09 May 2002 16:29 EDT
> Jim O'Brien, UnitOOPS Software

Other Threads