Excel Range Value issue (Excel 97 Vs Excel 2003)

[Keeno]

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

 

[Peter T]

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

 

[keepITcool]

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 :

 

[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

 

[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

 

[keepITcool]

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 :

 

[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

 

[Keeno]

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

 

[keepITcool]

Sorry for delay..

You kept talking about a date and two floats,
so I wrongly? assumed you had a true date.

But it looks to me the the array element has
a String data type not a Date data type..

Check the data type in the array vntArray(4)
in the locals window. Is that a string?

'===============================================================
The preferred method is to load a Date datatype into the variant
(or a Double dataType) and avoid strings.
'===============================================================


Else you'll get into conversion issues and vba OR excel must guess

like some dogs.. eager to please...but not so smart
(or too smart maybe


On my system (with a DMY regional setting)
?Format(cdate("04/05/2006"), "dd-mm-yyyy") => 04-05-2006
?Format(cdate("04/25/2006"), "dd-mm-yyyy") => 25-04-2006

first is interpreted as vba's favourite.. the locale DMY....
the second: vba finds that DMY is not possible and does a MDY

'==========
Excel behaves separately (and differently) from VBA
and as you noted xl97 and xl2003 have different interpretations
'==========

So you'll want to take the guesswork out of the equation..
once you have a Date data type you can assign it to Excel's range
without problems. (provided the range does not have a TEXT(@)
numberformat..

Function DateValueMDY(sMDY$) As Date
'converts a mm/dd/yyyy string to a date
If Application.International(xlMDY) Then
'locale = (MDY)input => trust vba's conversion
DateValueMDY = DateValue(sMDY)
Else
'locale <> (MDY)input => manual conversion
If InStr(sMDY, "/") <> 0 And Len(sMDY) = 10 Then
'yyyy
DateValueMDY = DateSerial(CInt(Mid(sMDY, 7, 4)), _
CInt(Mid(sMDY, 1, 2)), CInt(Mid(sMDY, 4, 2)))
ElseIf InStr(sMDY, "/") <> 0 And Len(sMDY) = 8 Then
'yy
DateValueMDY = DateSerial(CInt(Mid(sMDY, 7, 2)), _
CInt(Mid(sMDY, 1, 2)), CInt(Mid(sMDY, 4, 2)))
Else
Err.Raise (93) 'invalid pattern string
End If
End If
End Function



A small note re your syntax in a previous post:
you used:
xlRng.Value(4) = vntArray(4)

better to use:
xlRng.Cells(1,4).Value = vntArray(4)


Hope this helps.... but this IS confusing.


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


Keeno wrote :

 

[Keeno]

Hi keepITcool,
Thanks for your reply.

You're spot on, the "date" element in the variant array is a string
type.

So to get this working in Excel 2003 (as I mentioned in a previous
post) I cast it to a Date using CDate and also formatted it.

This solved the problem interfacing with Excel 2003, but the problem
then manifested itself in Excel 97 which had previously worked when the
"date" element was a string type.

You're right, this is confusing.

Keeno