CopyFromRecordSet changes cell formatting




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?

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.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question