Problem copying ADO recordset to Excel 2003

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.