Excel Range Value issue (Excel 97 Vs Excel 2003)
 

Hi,
I have some VB6 code which was originally written to populate a
spreadsheet in EXCEL 97. Basically, to cut a long story short,
what the code does is it populates a variant array by calling a stored
procedure. The variant array in question is populated with 3 elements
(a date, and 2 floats).
The array is then passed to Excel like this.

Dim xlRng As Excel.Range
xlRng.Value = vntDataArray

In my particular scenario, the first four rows have the following dates
in the "dd/mm/yyyy" format.
29/11/1995
30/11/1995
01/12/1995
04/12/1995

These dates are exported perfectly to Excel 97, but recently I've
upgraded to Excel 2003 and the dates are exported like this.

29/11/1995 - Excel Interprets these as strings
30/11/1995 - Excel Interprets these as strings
12/01/1995 - Excel Interprets these as Dates
12/04/1995 - Excel Interprets these as Dates

To get this working with Excel 2003, I've done a CDate case on the
dates and this actually works, but unfortunately when I then run my new
code on a machine that only has Excel 97 (which previously worked), the
dates are again like this

29/11/1995 - Excel Interprets these as strings
30/11/1995 - Excel Interprets these as strings
12/01/1995 - Excel Interprets these as Dates
12/04/1995 - Excel Interprets these as Dates

Any Ideas folks?

It would be very much appreciated.

Keeno

I don't think it's the difference between the versions of excel. I'm guessing
that it's the difference in the windows setting for how you enter dates.

Windows start button|settings|control panel|regional settings applet

And make sure that the date tab shows dd/mm/yyyy for the "offending" pc.

Keeno wrote:

Hi,
I have some VB6 code which was originally written to populate a
spreadsheet in EXCEL 97. Basically, to cut a long story short,
what the code does is it populates a variant array by calling a stored
procedure. The variant array in question is populated with 3 elements
(a date, and 2 floats).
The array is then passed to Excel like this.

Dim xlRng As Excel.Range
xlRng.Value = vntDataArray

In my particular scenario, the first four rows have the following dates
in the "dd/mm/yyyy" format.
29/11/1995
30/11/1995
01/12/1995
04/12/1995

These dates are exported perfectly to Excel 97, but recently I've
upgraded to Excel 2003 and the dates are exported like this.

29/11/1995 - Excel Interprets these as strings
30/11/1995 - Excel Interprets these as strings
12/01/1995 - Excel Interprets these as Dates
12/04/1995 - Excel Interprets these as Dates

To get this working with Excel 2003, I've done a CDate case on the
dates and this actually works, but unfortunately when I then run my new
code on a machine that only has Excel 97 (which previously worked), the
dates are again like this

29/11/1995 - Excel Interprets these as strings
30/11/1995 - Excel Interprets these as strings
12/01/1995 - Excel Interprets these as Dates
12/04/1995 - Excel Interprets these as Dates

Any Ideas folks?

It would be very much appreciated.

Keeno

--

Dave Peterson

I saw your response to your other thread.

Sorry.

Dave Peterson wrote:

I don't think it's the difference between the versions of excel. I'm guessing
that it's the difference in the windows setting for how you enter dates.

Windows start button|settings|control panel|regional settings applet

And make sure that the date tab shows dd/mm/yyyy for the "offending" pc.

Keeno wrote:

Hi,
I have some VB6 code which was originally written to populate a
spreadsheet in EXCEL 97. Basically, to cut a long story short,
what the code does is it populates a variant array by calling a stored
procedure. The variant array in question is populated with 3 elements
(a date, and 2 floats).
The array is then passed to Excel like this.

Dim xlRng As Excel.Range
xlRng.Value = vntDataArray

In my particular scenario, the first four rows have the following dates
in the "dd/mm/yyyy" format.
29/11/1995
30/11/1995
01/12/1995
04/12/1995

These dates are exported perfectly to Excel 97, but recently I've
upgraded to Excel 2003 and the dates are exported like this.

29/11/1995 - Excel Interprets these as strings
30/11/1995 - Excel Interprets these as strings
12/01/1995 - Excel Interprets these as Dates
12/04/1995 - Excel Interprets these as Dates

To get this working with Excel 2003, I've done a CDate case on the
dates and this actually works, but unfortunately when I then run my new
code on a machine that only has Excel 97 (which previously worked), the
dates are again like this

29/11/1995 - Excel Interprets these as strings
30/11/1995 - Excel Interprets these as strings
12/01/1995 - Excel Interprets these as Dates
12/04/1995 - Excel Interprets these as Dates

Any Ideas folks?

It would be very much appreciated.

Keeno

--

Dave Peterson

--

Dave Peterson