Change regional date settings using VBA Code

Hi
When I run my VBA code, I want to automatically change the regional date settings.  Is there a way i can do this in my Excel VBA code?
Thanks
JL
jlfourieAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
I cleaned up the code.

Kevin
Restart-Excel-Example.xls
0
 
zorvek (Kevin Jones)ConsultantCommented:
Here is some sample code. Note that Excel will not recognize the new date format until it is restarted.

Private Const LOCALE_SSHORTDATE = &H1F
Private Const HWND_BROADCAST = &HFFFF&
Private Const WM_SETTINGCHANGE = &H1A
 
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" ( _
        ByVal Locale As Long, _
        ByVal LCType As Long, _
        ByVal lpLCData As String, _
        ByVal cchData As Long _
    ) As Long

Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()

Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" ( _
        ByVal hwnd As Long, _
        ByVal wMsg As Long, _
        ByVal wParam As Long, _
        ByVal lParam As Long _
    ) As Long

Private Declare Function SetLocaleInfo Lib "kernel32" Alias "SetLocaleInfoA" ( _
        ByVal Locale As Long, _
        ByVal LCType As Long, _
        ByVal lpLCData As String _
    ) As Boolean

Public Sub ChangeRegionalShortDateFormat()

    Dim Locale As Long
    Dim LocalInfo As String
    Dim Result As Long
    Dim Position As Long
    Dim blnSuccess As Boolean
   
    Locale = GetUserDefaultLCID()
    LocalInfo = String(256, 0)
    Result = GetLocaleInfo(Locale, LOCALE_SSHORTDATE, LocalInfo, Len(LocalInfo))
    If Result > 0 Then
        Position = InStr(LocalInfo, Chr(0))
        If LCase(Left(LocalInfo, Position - 1)) <> "mm/dd/yyyy" Then
            SetLocaleInfo Locale, LOCALE_SSHORTDATE, "MM/dd/yyyy"
            PostMessage HWND_BROADCAST, WM_SETTINGCHANGE, 0, 0
        End If
    End If

End Sub

Kevin
0
 
jlfourieAuthor Commented:
Hi Kevin
Thanks, your code works well.  I've adjusted it to store current setting, so that I can change back to original settings afterwards.
I also realise that you have to restart Excel, because even though the settings are changed, they don't seem to take effect.  Is there any way around this?  Any way to "force" settings to take effect without closing Excel?
If not, is there some way to close Excel, and then re-open workbook again, all within code?
Thanks
JL
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
zorvek (Kevin Jones)ConsultantCommented:
There is no known way to force Excel to consider the new locale settings without restarting the application.

You will have to restart it.

Attached is a sample workbook illustrating one way to do this. It isn't real pretty but it works.

Kevin

Example.xls
0
 
jlfourieAuthor Commented:
Hi Kevin
Thanks, this is brilliant.  Just two comments:
1) When I ran it the first time, it said my Personal file was still active.  Seems as if it didn't quite release full Excel, but opened second version before first closed.  Does that have to do with saving as read only?
2) There's still an Excel pop up box that comes up every time, even once closed and opened up excel again.
Regards
JL
0
 
jlfourieAuthor Commented:
Thanks, tried running cleaned up code...  The first time I ran it, there was an error at
 Shell "Excel.exe """ & ThisWorkbook.FullName & """"
I can't reproduce that error.  (Third last line in code, copied below)
At least my problem in point 1) above has been sorted.
I still get the excel pop up every time I open the new file.
Regards
JL
=======
Public Sub RestartExcel(Optional ByVal ExcelRestartStep As Variant)

    SetExcelRestartStep ExcelRestartStep
   
    On Error Resume Next
    ThisWorkbook.Names(mcExcelRestartWindowStateVariableName).Delete
    ThisWorkbook.Names.Add mcExcelRestartWindowStateVariableName, Application.WindowState
    On Error GoTo 0
   
    ThisWorkbook.Save
    ThisWorkbook.ChangeFileAccess xlReadOnly
    Shell "Excel.exe """ & ThisWorkbook.FullName & """"         'This is where I got first error
    Application.Quit

End Sub
0
 
jlfourieAuthor Commented:
Hi Kevin
Please ignore my comment regarding the pop up box, that works perfectly.
I'll also try and get more detail on another computer to reproduce error, and will send that when I have it.
Thanks again for your excellent help.
Regards
JL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.