CopyFromRecordSet changes cell formatting
 

Hi!

I know CopyFromRecordSet has caused problems to many people out there,
but I haven't found any references to this particular case:

We're using a VB6-app to automate Excel 2002. We use CopyFromRecordset
to read data from a stored proc in SQL 2000 into cells in a named area
of a worksheet.

The problem is that the format automagically for some of the cells that
we read data into. We have three columns, all formatted as Number with
3 decimals and a thousand-separator.
The source columns in SQL are decimal(15,3)
When we read the data, using CopyFromRecordset, two of the columns
remain their format (i.e. numeric). But one of the columns become a
date formatted cell, causing us lots of problems, of course.

When trouble-shooting, we noticed that this occurs even if the
resultset from the stored proc is empty (i.e. a cell in the first row
of the named area gets formatted to date).


Any ideas?

Thanks!
/Fredrik
ps. This may sound like an easy problem to solve - we can of course
programatically format the columns after the import. However, we have
lots of different Excel-worksheets that we work with, and we need to
get to the bottom with this.