Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format an Excel Column in the window's short date format.
I'm trying to use com to format an Excel column as a date in window's short
date format. It works for most regional settings but not Norwegian(Bokmal). To test this I set my regional settings to Norwegian(Bokmal) in control pannel. ( I have US windows xp and excel 2003 ) I call GetLocaleInfoW() to get the regional window's short date setting and it returns 'dd.MM.yyyy' which is correct I believe. I then set the NumberFormat Property of the Excel Column object to 'dd.MM.yyyy' by converting the unicode received from GetLocaleInfoW to a bstring. To my surprise a date such as 12 January 2006 appears in that column as 12.01.yyyy instead of 12.01.2006 as I expected. using the NumberFormatLocal format produced the same result. It appears that when regional settings are set to Norwegian(Bokmal) 'yyyy' is not the correct date formating code for the year portion of the date. If anyone can point me in the right direction on how to format a column for the window's short date format for various regional settings, I'd greatly appreciate it. using the date string from GetLocaleInfoW() isnt a useful approach. I was hoping there was a format string that allowed setting the date format for a column to the window's short date format without having to construct a string such as 'dd.MM.yyyy' which appears problematic. Jim Kane |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format an Excel Column in the window's short date format.
Jim,
This works for me in VBA. Maybe your UnicodeBString code is not doing what you expect ? Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As Long, ByVal cchData As Long) As Long Private Sub CommandButton1_Click() Dim Buffer As String, Ret As Long Dim GetInfo As String Const LOCALE_USER_DEFAULT = &H400 Const LOCALE_SSHORTDATE As Long = &H1F Buffer = String$(256, 0) Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, StrPtr(Buffer), Len(Buffer)) If Ret 0 Then GetInfo = Left$(Buffer, Ret - 1) Else GetInfo = "" Exit Sub End If MsgBox GetInfo Range("A1").Value = CLng(Now()) Range("A1").NumberFormat = GetInfo Debug.Print Range("A1").Text End Sub "jim kane" wrote in message ... I'm trying to use com to format an Excel column as a date in window's short date format. It works for most regional settings but not Norwegian(Bokmal). To test this I set my regional settings to Norwegian(Bokmal) in control pannel. ( I have US windows xp and excel 2003 ) I call GetLocaleInfoW() to get the regional window's short date setting and it returns 'dd.MM.yyyy' which is correct I believe. I then set the NumberFormat Property of the Excel Column object to 'dd.MM.yyyy' by converting the unicode received from GetLocaleInfoW to a bstring. To my surprise a date such as 12 January 2006 appears in that column as 12.01.yyyy instead of 12.01.2006 as I expected. using the NumberFormatLocal format produced the same result. It appears that when regional settings are set to Norwegian(Bokmal) 'yyyy' is not the correct date formating code for the year portion of the date. If anyone can point me in the right direction on how to format a column for the window's short date format for various regional settings, I'd greatly appreciate it. using the date string from GetLocaleInfoW() isnt a useful approach. I was hoping there was a format string that allowed setting the date format for a column to the window's short date format without having to construct a string such as 'dd.MM.yyyy' which appears problematic. Jim Kane |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format an Excel Column in the window's short date format.
Nick:
Thanks for the reply but are you useing Norwegian(Bokmal) regional settings? The approach works in general but not for that region where the .yyyy returned from GetLocaleInfo is not accepted as year formating. My bstring conversion is a simple call to SysAllocString(). Jim Kane "NickHK" wrote in message ... Jim, This works for me in VBA. Maybe your UnicodeBString code is not doing what you expect ? Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As Long, ByVal cchData As Long) As Long Private Sub CommandButton1_Click() Dim Buffer As String, Ret As Long Dim GetInfo As String Const LOCALE_USER_DEFAULT = &H400 Const LOCALE_SSHORTDATE As Long = &H1F Buffer = String$(256, 0) Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, StrPtr(Buffer), Len(Buffer)) If Ret 0 Then GetInfo = Left$(Buffer, Ret - 1) Else GetInfo = "" Exit Sub End If MsgBox GetInfo Range("A1").Value = CLng(Now()) Range("A1").NumberFormat = GetInfo Debug.Print Range("A1").Text End Sub "jim kane" wrote in message ... I'm trying to use com to format an Excel column as a date in window's short date format. It works for most regional settings but not Norwegian(Bokmal). To test this I set my regional settings to Norwegian(Bokmal) in control pannel. ( I have US windows xp and excel 2003 ) I call GetLocaleInfoW() to get the regional window's short date setting and it returns 'dd.MM.yyyy' which is correct I believe. I then set the NumberFormat Property of the Excel Column object to 'dd.MM.yyyy' by converting the unicode received from GetLocaleInfoW to a bstring. To my surprise a date such as 12 January 2006 appears in that column as 12.01.yyyy instead of 12.01.2006 as I expected. using the NumberFormatLocal format produced the same result. It appears that when regional settings are set to Norwegian(Bokmal) 'yyyy' is not the correct date formating code for the year portion of the date. If anyone can point me in the right direction on how to format a column for the window's short date format for various regional settings, I'd greatly appreciate it. using the date string from GetLocaleInfoW() isnt a useful approach. I was hoping there was a format string that allowed setting the date format for a column to the window's short date format without having to construct a string such as 'dd.MM.yyyy' which appears problematic. Jim Kane |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format an Excel Column in the window's short date format.
Jim,
Yes, the MsgBox shows "dd.MM.yyyy". With XL2000 & XL2002. NickHK "jim kane" wrote in message ... Nick: Thanks for the reply but are you useing Norwegian(Bokmal) regional settings? The approach works in general but not for that region where the .yyyy returned from GetLocaleInfo is not accepted as year formating. My bstring conversion is a simple call to SysAllocString(). Jim Kane "NickHK" wrote in message ... Jim, This works for me in VBA. Maybe your UnicodeBString code is not doing what you expect ? Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As Long, ByVal cchData As Long) As Long Private Sub CommandButton1_Click() Dim Buffer As String, Ret As Long Dim GetInfo As String Const LOCALE_USER_DEFAULT = &H400 Const LOCALE_SSHORTDATE As Long = &H1F Buffer = String$(256, 0) Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, StrPtr(Buffer), Len(Buffer)) If Ret 0 Then GetInfo = Left$(Buffer, Ret - 1) Else GetInfo = "" Exit Sub End If MsgBox GetInfo Range("A1").Value = CLng(Now()) Range("A1").NumberFormat = GetInfo Debug.Print Range("A1").Text End Sub "jim kane" wrote in message ... I'm trying to use com to format an Excel column as a date in window's short date format. It works for most regional settings but not Norwegian(Bokmal). To test this I set my regional settings to Norwegian(Bokmal) in control pannel. ( I have US windows xp and excel 2003 ) I call GetLocaleInfoW() to get the regional window's short date setting and it returns 'dd.MM.yyyy' which is correct I believe. I then set the NumberFormat Property of the Excel Column object to 'dd.MM.yyyy' by converting the unicode received from GetLocaleInfoW to a bstring. To my surprise a date such as 12 January 2006 appears in that column as 12.01.yyyy instead of 12.01.2006 as I expected. using the NumberFormatLocal format produced the same result. It appears that when regional settings are set to Norwegian(Bokmal) 'yyyy' is not the correct date formating code for the year portion of the date. If anyone can point me in the right direction on how to format a column for the window's short date format for various regional settings, I'd greatly appreciate it. using the date string from GetLocaleInfoW() isnt a useful approach. I was hoping there was a format string that allowed setting the date format for a column to the window's short date format without having to construct a string such as 'dd.MM.yyyy' which appears problematic. Jim Kane |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format an Excel Column in the window's short date format.
The MsgBox is meaningless. I display the same string. but when I use that
string to set the numberformat if my regional settings are set to Norwegian dates subsequently do not display correctly. I take it you have no idea why. Thanks anyway I'll repost Jim Kane "NickHK" wrote in message ... Jim, Yes, the MsgBox shows "dd.MM.yyyy". With XL2000 & XL2002. NickHK "jim kane" wrote in message ... Nick: Thanks for the reply but are you useing Norwegian(Bokmal) regional settings? The approach works in general but not for that region where the .yyyy returned from GetLocaleInfo is not accepted as year formating. My bstring conversion is a simple call to SysAllocString(). Jim Kane "NickHK" wrote in message ... Jim, This works for me in VBA. Maybe your UnicodeBString code is not doing what you expect ? Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As Long, ByVal cchData As Long) As Long Private Sub CommandButton1_Click() Dim Buffer As String, Ret As Long Dim GetInfo As String Const LOCALE_USER_DEFAULT = &H400 Const LOCALE_SSHORTDATE As Long = &H1F Buffer = String$(256, 0) Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, StrPtr(Buffer), Len(Buffer)) If Ret 0 Then GetInfo = Left$(Buffer, Ret - 1) Else GetInfo = "" Exit Sub End If MsgBox GetInfo Range("A1").Value = CLng(Now()) Range("A1").NumberFormat = GetInfo Debug.Print Range("A1").Text End Sub "jim kane" wrote in message ... I'm trying to use com to format an Excel column as a date in window's short date format. It works for most regional settings but not Norwegian(Bokmal). To test this I set my regional settings to Norwegian(Bokmal) in control pannel. ( I have US windows xp and excel 2003 ) I call GetLocaleInfoW() to get the regional window's short date setting and it returns 'dd.MM.yyyy' which is correct I believe. I then set the NumberFormat Property of the Excel Column object to 'dd.MM.yyyy' by converting the unicode received from GetLocaleInfoW to a bstring. To my surprise a date such as 12 January 2006 appears in that column as 12.01.yyyy instead of 12.01.2006 as I expected. using the NumberFormatLocal format produced the same result. It appears that when regional settings are set to Norwegian(Bokmal) 'yyyy' is not the correct date formating code for the year portion of the date. If anyone can point me in the right direction on how to format a column for the window's short date format for various regional settings, I'd greatly appreciate it. using the date string from GetLocaleInfoW() isnt a useful approach. I was hoping there was a format string that allowed setting the date format for a column to the window's short date format without having to construct a string such as 'dd.MM.yyyy' which appears problematic. Jim Kane |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format an Excel Column in the window's short date format.
Jim,
I mean the format string used is correctly assigned as "dd.MM.yyyy" and the resulting formatted date appears correctly as "25.01.2007". So in the VBA code I posted everything works as expected on an English W2K system with English XL2002. However, in the Excel Immediate window, if you do: ?Application.International(xlDayCode) d ?Application.International(xlMonthCode) m ?Application.International(xlYearCode) å Not sure what effect this has on your code. NickHK "jim kane" wrote in message ... The MsgBox is meaningless. I display the same string. but when I use that string to set the numberformat if my regional settings are set to Norwegian dates subsequently do not display correctly. I take it you have no idea why. Thanks anyway I'll repost Jim Kane "NickHK" wrote in message ... Jim, Yes, the MsgBox shows "dd.MM.yyyy". With XL2000 & XL2002. NickHK "jim kane" wrote in message ... Nick: Thanks for the reply but are you useing Norwegian(Bokmal) regional settings? The approach works in general but not for that region where the .yyyy returned from GetLocaleInfo is not accepted as year formating. My bstring conversion is a simple call to SysAllocString(). Jim Kane "NickHK" wrote in message ... Jim, This works for me in VBA. Maybe your UnicodeBString code is not doing what you expect ? Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As Long, ByVal cchData As Long) As Long Private Sub CommandButton1_Click() Dim Buffer As String, Ret As Long Dim GetInfo As String Const LOCALE_USER_DEFAULT = &H400 Const LOCALE_SSHORTDATE As Long = &H1F Buffer = String$(256, 0) Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, StrPtr(Buffer), Len(Buffer)) If Ret 0 Then GetInfo = Left$(Buffer, Ret - 1) Else GetInfo = "" Exit Sub End If MsgBox GetInfo Range("A1").Value = CLng(Now()) Range("A1").NumberFormat = GetInfo Debug.Print Range("A1").Text End Sub "jim kane" wrote in message ... I'm trying to use com to format an Excel column as a date in window's short date format. It works for most regional settings but not Norwegian(Bokmal). To test this I set my regional settings to Norwegian(Bokmal) in control pannel. ( I have US windows xp and excel 2003 ) I call GetLocaleInfoW() to get the regional window's short date setting and it returns 'dd.MM.yyyy' which is correct I believe. I then set the NumberFormat Property of the Excel Column object to 'dd.MM.yyyy' by converting the unicode received from GetLocaleInfoW to a bstring. To my surprise a date such as 12 January 2006 appears in that column as 12.01.yyyy instead of 12.01.2006 as I expected. using the NumberFormatLocal format produced the same result. It appears that when regional settings are set to Norwegian(Bokmal) 'yyyy' is not the correct date formating code for the year portion of the date. If anyone can point me in the right direction on how to format a column for the window's short date format for various regional settings, I'd greatly appreciate it. using the date string from GetLocaleInfoW() isnt a useful approach. I was hoping there was a format string that allowed setting the date format for a column to the window's short date format without having to construct a string such as 'dd.MM.yyyy' which appears problematic. Jim Kane |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format an Excel Column in the window's short date format.
Yes, I found the same thing thanks to help I recieved on the
Comp.Lang.Clarion newsgroup. (Clarion is my favorite programming language.) For the specific region I was working on Norwegian(Bokmul) Windows (GetLocaleInfo()) returned: dd.MM.yyyy Excel Internation properties lead me to construct: dd.mm.åååå Using US English Windows and Excel, if I ignore what control panel says and use the International(xlWhatever ) properties and send the date format string I build from the excel properties to excel via the 'NumberFormat' and not the 'NumberFormatLocal' Property then it works. Feeding info from Windows Control Panel like I was doing into excel did not work. You would think the company that built windows could talk to the company that built excel and get this straightened out. Apparently not. Jim Kane "NickHK" wrote in message ... Jim, I mean the format string used is correctly assigned as "dd.MM.yyyy" and the resulting formatted date appears correctly as "25.01.2007". So in the VBA code I posted everything works as expected on an English W2K system with English XL2002. However, in the Excel Immediate window, if you do: ?Application.International(xlDayCode) d ?Application.International(xlMonthCode) m ?Application.International(xlYearCode) å Not sure what effect this has on your code. NickHK "jim kane" wrote in message ... The MsgBox is meaningless. I display the same string. but when I use that string to set the numberformat if my regional settings are set to Norwegian dates subsequently do not display correctly. I take it you have no idea why. Thanks anyway I'll repost Jim Kane "NickHK" wrote in message ... Jim, Yes, the MsgBox shows "dd.MM.yyyy". With XL2000 & XL2002. NickHK "jim kane" wrote in message ... Nick: Thanks for the reply but are you useing Norwegian(Bokmal) regional settings? The approach works in general but not for that region where the .yyyy returned from GetLocaleInfo is not accepted as year formating. My bstring conversion is a simple call to SysAllocString(). Jim Kane "NickHK" wrote in message ... Jim, This works for me in VBA. Maybe your UnicodeBString code is not doing what you expect ? Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As Long, ByVal cchData As Long) As Long Private Sub CommandButton1_Click() Dim Buffer As String, Ret As Long Dim GetInfo As String Const LOCALE_USER_DEFAULT = &H400 Const LOCALE_SSHORTDATE As Long = &H1F Buffer = String$(256, 0) Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, StrPtr(Buffer), Len(Buffer)) If Ret 0 Then GetInfo = Left$(Buffer, Ret - 1) Else GetInfo = "" Exit Sub End If MsgBox GetInfo Range("A1").Value = CLng(Now()) Range("A1").NumberFormat = GetInfo Debug.Print Range("A1").Text End Sub "jim kane" wrote in message ... I'm trying to use com to format an Excel column as a date in window's short date format. It works for most regional settings but not Norwegian(Bokmal). To test this I set my regional settings to Norwegian(Bokmal) in control pannel. ( I have US windows xp and excel 2003 ) I call GetLocaleInfoW() to get the regional window's short date setting and it returns 'dd.MM.yyyy' which is correct I believe. I then set the NumberFormat Property of the Excel Column object to 'dd.MM.yyyy' by converting the unicode received from GetLocaleInfoW to a bstring. To my surprise a date such as 12 January 2006 appears in that column as 12.01.yyyy instead of 12.01.2006 as I expected. using the NumberFormatLocal format produced the same result. It appears that when regional settings are set to Norwegian(Bokmal) 'yyyy' is not the correct date formating code for the year portion of the date. If anyone can point me in the right direction on how to format a column for the window's short date format for various regional settings, I'd greatly appreciate it. using the date string from GetLocaleInfoW() isnt a useful approach. I was hoping there was a format string that allowed setting the date format for a column to the window's short date format without having to construct a string such as 'dd.MM.yyyy' which appears problematic. Jim Kane |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format an Excel Column in the window's short date format.
Jim,
This localisation side to Windows and an app's integration with it becomes confusing quickly, for me anyway. Glad you got it sorted anyway. NickHK "jim kane" wrote in message ... Yes, I found the same thing thanks to help I recieved on the Comp.Lang.Clarion newsgroup. (Clarion is my favorite programming language.) For the specific region I was working on Norwegian(Bokmul) Windows (GetLocaleInfo()) returned: dd.MM.yyyy Excel Internation properties lead me to construct: dd.mm.åååå Using US English Windows and Excel, if I ignore what control panel says and use the International(xlWhatever ) properties and send the date format string I build from the excel properties to excel via the 'NumberFormat' and not the 'NumberFormatLocal' Property then it works. Feeding info from Windows Control Panel like I was doing into excel did not work. You would think the company that built windows could talk to the company that built excel and get this straightened out. Apparently not. Jim Kane "NickHK" wrote in message ... Jim, I mean the format string used is correctly assigned as "dd.MM.yyyy" and the resulting formatted date appears correctly as "25.01.2007". So in the VBA code I posted everything works as expected on an English W2K system with English XL2002. However, in the Excel Immediate window, if you do: ?Application.International(xlDayCode) d ?Application.International(xlMonthCode) m ?Application.International(xlYearCode) å Not sure what effect this has on your code. NickHK "jim kane" wrote in message ... The MsgBox is meaningless. I display the same string. but when I use that string to set the numberformat if my regional settings are set to Norwegian dates subsequently do not display correctly. I take it you have no idea why. Thanks anyway I'll repost Jim Kane "NickHK" wrote in message ... Jim, Yes, the MsgBox shows "dd.MM.yyyy". With XL2000 & XL2002. NickHK "jim kane" wrote in message ... Nick: Thanks for the reply but are you useing Norwegian(Bokmal) regional settings? The approach works in general but not for that region where the ..yyyy returned from GetLocaleInfo is not accepted as year formating. My bstring conversion is a simple call to SysAllocString(). Jim Kane "NickHK" wrote in message ... Jim, This works for me in VBA. Maybe your UnicodeBString code is not doing what you expect ? Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As Long, ByVal cchData As Long) As Long Private Sub CommandButton1_Click() Dim Buffer As String, Ret As Long Dim GetInfo As String Const LOCALE_USER_DEFAULT = &H400 Const LOCALE_SSHORTDATE As Long = &H1F Buffer = String$(256, 0) Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, StrPtr(Buffer), Len(Buffer)) If Ret 0 Then GetInfo = Left$(Buffer, Ret - 1) Else GetInfo = "" Exit Sub End If MsgBox GetInfo Range("A1").Value = CLng(Now()) Range("A1").NumberFormat = GetInfo Debug.Print Range("A1").Text End Sub "jim kane" wrote in message ... I'm trying to use com to format an Excel column as a date in window's short date format. It works for most regional settings but not Norwegian(Bokmal). To test this I set my regional settings to Norwegian(Bokmal) in control pannel. ( I have US windows xp and excel 2003 ) I call GetLocaleInfoW() to get the regional window's short date setting and it returns 'dd.MM.yyyy' which is correct I believe. I then set the NumberFormat Property of the Excel Column object to 'dd.MM.yyyy' by converting the unicode received from GetLocaleInfoW to a bstring. To my surprise a date such as 12 January 2006 appears in that column as 12.01.yyyy instead of 12.01.2006 as I expected. using the NumberFormatLocal format produced the same result. It appears that when regional settings are set to Norwegian(Bokmal) 'yyyy' is not the correct date formating code for the year portion of the date. If anyone can point me in the right direction on how to format a column for the window's short date format for various regional settings, I'd greatly appreciate it. using the date string from GetLocaleInfoW() isnt a useful approach. I was hoping there was a format string that allowed setting the date format for a column to the window's short date format without having to construct a string such as 'dd.MM.yyyy' which appears problematic. Jim Kane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why can't I convert Long date format to Short date | Excel Discussion (Misc queries) | |||
Storing data in Short Date format | New Users to Excel | |||
OFC 07 wrkbk saved short date format changes to general when reop | Excel Discussion (Misc queries) | |||
short date format not working while i try to excel afte | Excel Discussion (Misc queries) | |||
one column on my excel spreadsheet will NOT format the date | Excel Worksheet Functions |