Problem copying ADO recordset to Excel 2003

I have a number of older spreadsheet applications which use the recordset
GetRows() method to fetch recordset data into a variant array and then use
Range("Data").Value = varData()
to get the data into Excel.

MS KnowledgeBase article: 246335 - "How To Transfer Data from an ADO
Recordset to Excel with Automation" gives an example of doing this. It also
recommends the newer CopyFromRecordset technique for later versions of Excel.

I have many older apps that use the Array technique, which I don't want to
re-code at this time.

The problem is that in Excel 2003, any date fields are interpreted as
American dates (mm/dd/yy) rather than Australian dates (dd/mm/yy) when the
data is put into the worksheet by Range("Data").Value = varData() . Excel
2000 did not have the same problem.

Has anyone else had this problem, and know of a solution?

The newer CopyFromRecordset technique works, but it will mean a lot of
re-coding.

You need to ensure that the date setting in regional settings are 'correct'
for Australia & set the Language to 'what?' for Australia, perhaps UK?

"Peter Carr" wrote:

> I have a number of older spreadsheet applications which use the recordset
> GetRows() method to fetch recordset data into a variant array and then use
> Range("Data").Value = varData()
> to get the data into Excel.
>
> MS KnowledgeBase article: 246335 - "How To Transfer Data from an ADO
> Recordset to Excel with Automation" gives an example of doing this. It also
> recommends the newer CopyFromRecordset technique for later versions of Excel.
>
> I have many older apps that use the Array technique, which I don't want to
> re-code at this time.
>
> The problem is that in Excel 2003, any date fields are interpreted as
> American dates (mm/dd/yy) rather than Australian dates (dd/mm/yy) when the
> data is put into the worksheet by Range("Data").Value = varData() . Excel
> 2000 did not have the same problem.
>
> Has anyone else had this problem, and know of a solution?
>
> The newer CopyFromRecordset technique works, but it will mean a lot of
> re-coding.

Yes, the regional settings are correct. The language setting is English
(Australia). It's nice to know that Microsoft know that we exist "down
under".

I believe this is a new problem introduced with Excel 2003. We haven't
installed SP1 yet, although I haven't seen anything to suggest that this
issue is addressed in it.

"AA2e72E" wrote:

> You need to ensure that the date setting in regional settings are 'correct'
> for Australia & set the Language to 'what?' for Australia, perhaps UK?
>
> "Peter Carr" wrote:
>
> > I have a number of older spreadsheet applications which use the recordset
> > GetRows() method to fetch recordset data into a variant array and then use
> > Range("Data").Value = varData()
> > to get the data into Excel.
> >
> > MS KnowledgeBase article: 246335 - "How To Transfer Data from an ADO
> > Recordset to Excel with Automation" gives an example of doing this. It also
> > recommends the newer CopyFromRecordset technique for later versions of Excel.
> >
> > I have many older apps that use the Array technique, which I don't want to
> > re-code at this time.
> >
> > The problem is that in Excel 2003, any date fields are interpreted as
> > American dates (mm/dd/yy) rather than Australian dates (dd/mm/yy) when the
> > data is put into the worksheet by Range("Data").Value = varData() . Excel
> > 2000 did not have the same problem.
> >
> > Has anyone else had this problem, and know of a solution?
> >
> > The newer CopyFromRecordset technique works, but it will mean a lot of
> > re-coding.