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

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

news:3cd9878e$1_2@dnews...

How about prepending = to the numeric ones?

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

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

news:VA.0000031e.3828a6df@remove-this-prefix.unitoops.com...