ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Excel Range Value issue (Excel 97 Vs Excel 2003) (https://www.excelbanter.com/excel-discussion-misc-queries/30396-excel-range-value-issue-excel-97-vs-excel-2003-a.html)

Keeno June 13th 05 11:55 AM

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


Dave Peterson June 13th 05 01:36 PM

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 June 13th 05 02:01 PM

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


All times are GMT +1. The time now is 02:41 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
ExcelBanter.com