Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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 : 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Range Value issue (Excel 97 Vs Excel 2003)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issue opening 2003 excel files in Excel 2007 | Excel Discussion (Misc queries) | |||
Data Import to Excel Issue with Excel 2007 and Excel 2003 on same | Excel Discussion (Misc queries) | |||
excel 2003 issue | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel 2003 Issue with UsedRange.Copy (code works in Excel 2002) | Excel Programming |