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.

Problem copying ADO recordset to Excel 2003
 

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.