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

Hi Keeno,

A guess - maybe your xl97 system date format is International "dd,mm,yy",
but your in your Excel 2003 is US "mm,dd,yy". If so it would not interpret
any number over 12 as a month. Back in XL97, CDate only works with US date
format (I think).

Regards,
Peter T

"Keeno" wrote in message
ups.com...
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

Hi Peter T,
Thanks for the reply, but the system date format is "dd/mm/yyyy" on
my PC which has Excel 2003. And the date format is also "dd/mm/yyyy" on
the old PC which just has Excel 97 installed.

Thanks anyway for your prompt reply,

Keeno

is Reading or Writing the problem?

writing international dates:

assign a double to the cells' value2 property
optionally format the range by setting the numberformat



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


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

Hi keepITcool,
Been there done that. Thanks for the reply anyway.

to answer your question, I'm literally passing a variant array to the
Value propery
of the Range Object in Excel i.e.

xlRng.Value = vntDataArray

the contents of the third element of the variant array is

vntDataArray(2) = '01/12/1995'

but after the assignment

xlRng.Value(2) = '#12/01/1995#'

Hope this clarifies it.

It's as if the Value property of the Range object is performing some
sort of conversion
in Excel 2003 whereas with Excel 97 it didn't.

Regards,

Keeno

you are confused

not Value(2) but Value2

Sub foo()
Dim va(1 To 3)
va(1) = Date
va(2) = Date - 7
va(3) = Date - 14
ActiveCell.Resize(, 3).Value2 = va
End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Keeno wrote :

Hi keepITcool,
Been there done that. Thanks for the reply anyway.

to answer your question, I'm literally passing a variant array to the
Value propery
of the Range Object in Excel i.e.

xlRng.Value = vntDataArray

the contents of the third element of the variant array is

vntDataArray(2) = '01/12/1995'

but after the assignment

xlRng.Value(2) = '#12/01/1995#'

Hope this clarifies it.

It's as if the Value property of the Range object is performing some
sort of conversion
in Excel 2003 whereas with Excel 97 it didn't.

Regards,

Keeno

I think we're getting crossed wires here.

Let me remove this confusion from this previous post by rephrasing it

xlRng.Value = vntDataArray


the contents of the fourth and fifth element of the variant array is


vntDataArray(3) = '04/12/1995'
vntDataArray(4) = '05/12/1995'
and so on

but after the assignment


xlRng.Value(3) = '#12/04/1995#'
xlRng.Value(4) = '#12/05/1995#'
and so on

Hope this clarifies it.


It's as if the Value property of the Range object is performing some
sort of conversion
in Excel 2003 whereas with Excel 97 it didn't.

Hi,
Just to add to the previous post....

I have traced through VB6 App on both machines (i.e my PC which has
Excel 2003
and another PC which has Excel 97)

and on both PCs, before the following assingment, the date contents of
vntDateArray
are in the format "dd/mm/yyyyy" e.g. vntDataArray(4, 0) = '01/12/1995'
and vntDataArray(5, 0) = '04/12/1995'

xlRng.Value = vntDataArray

However, after this assignment (when the code runs on the PC with Excel
2003) the
contents of xlRng.Value(4, 0) = '12/01/1995' and xlRng.Value(5, 0) =
'12/04/1995'.
Bizarrely, on the machine with Excel 97, after the assignment the
contents are unchanged i.e. xlRng.Value(4, 0) = '01/12/1995' and
xlRng(5, 0) = '04/12/1995'.
Again, Regional Settings are identical on each PC. I've also tried the
xlRng.NumberFormat option.

Can someone give me a solution before I lose my sanity?

Thanks,

Keeno