Modify windows language using VBA

[Alex St-Pierre]

Hi,
I have a program that modify the windows language inside Excel.

Call ExecCmd("rundll32.exe shell32.dll,Control_RunDLL intl.cpl" + ",,5")

When I execute this command, the language inside the control panel is
changed but the number displayed in Excel still show the old language. If I
open the control panel and change it manually (should gives same result), the
number displayed in Excel show the new language. Does anyone know why Excel
doesn't show the same result for both method ? It's like if there's a refresh
problem.
Thanks!
Alex

All codes:
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type

Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessID As Long
dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
lpStartupInfo As STARTUPINFO, lpProcessInformation As _
PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Public Function ExecCmd(cmdline$)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO

' Initialize the STARTUPINFO structure:
start.cb = Len(start)

' Start the shelled application:
Ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)

' Wait for the shelled application to finish:
Ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, Ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = Ret&
End Function

Sub Form_Click()


Dim retval As Long
'retval = ExecCmd("notepad.exe")
retval = ExecCmd("rundll32.exe shell32.dll,Control_RunDLL intl.cpl" +
",,5")
MsgBox "Process Finished, Exit Code " & retval
End Sub

 

[Chip Pearson]

I suspect that when you execute it via code, the Control Panel item isn't
sending a message to the application indicating a setting change. I just did
a quick test with SPY++, and it seems that when you change the locale
manually, Windows sends a WM_SETTINGCHANGE message to all top level windows.
The WPARAM is 0 for a change of locale, and the LPARAM is the new locale
info. You could attempt to send that message directly to Excel after
executing your code. E.g.,

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

Const WM_SETTINGCHANGE = &H1A
Const SPI_SETDEFAULTINPUTLANG = &H5A


Sub AAA()
Dim Res As Long
Res = SendMessage(hwnd:=Application.hwnd, _
wMsg:=WM_SETTINGCHANGE, wParam:=0&, lParam:=LParamValue)
End Sub

I'll leave it up to you to figure out the value of LParamValue. Have a look
at http://msdn2.microsoft.com/en-us/library/ms724947.aspx and
http://msdn2.microsoft.com/en-us/library/ms725497.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

 

[NickHK]

Alex,
Seems that the WaitForSingleObject code is interfering with Excel's update.
After this is run, if you right click the date cell in question and Format,
I see the locale does not match the formats available.
i.e. the locale is correctly updated in the Format tab, but the available
formats still match the previous locale. Hence the format of date does not
change.

I have noticed that tab 5 has 2 versions. Firstly I saw what looked like an
Office tab to Modify locale.
Then, on another running of the code, I saw the normal Windows one. Not sure
what is going on here.

Maybe the process is not that simple, but involves one/more of these calls
also:
SetThreadLocale Sets the calling thread's current locale.
SetThreadPreferredUILanguages Called to set the preferred thread user
interface (UI) languages.
SetThreadUILanguage Sets the user interface (UI) language in the thread
environment block.
But this is guesswork.

Without the WaitForSingleObject code, Excel updates as expected, although
then of course your code is not notified when the changes have been
completed, although your code cannot tell if the user cancelled either.
You could sub-class and watch for the settings change message or test the
current locale when important to know it.

Depends why you need to do this.

NickHK

 

[Alex St-Pierre]

Hi Nick!

I see the locale does not match the formats available.

Same thing happens on my computer.

Without the WaitForSingleObject code, Excel updates as expected

I tried to remove: 'Ret& = WaitForSingleObject(proc.hProcess, INFINITE) but
it gives the same answer. The following lines don’t work too:
Dim Cmdl As String
Cmdl = "rundll32.exe shell32.dll,Control_RunDLL intl.cpl" + ",,5"
Shell Cmdl$, vbNormalFocus

I’m wondering why
"rundll32.exe shell32.dll,Control_RunDLL intl.cpl" + ",,5" doesn’t give the
same answer as if I open it from the control panel?

I use excel to insert all parameters and thereafter, I create a word
document and make a report. When I copy the data into word, the system
language must be set to the right language to display all numbers correctly.
So, before executing it, I must be sure that the right language is used

At the beginning, I wanted to change the settings directly from VBA. It was
very complicated, so, I decided to 1) make a test is the local setting is ok
2) if not, put a message that explain how to change it 3) Open the box that
request the change.

At the execution of the report, I will give an error message and open the
right box and stop the macro thereafter (comeback to the initial form). So,
the user will have to click to “Execute report†a second time to create the
report. So, if all have been set correctly, the report will be created. If
not, the same error message will be displayed.

Could you send me the program you used when excel updates as expected? I
could only open the control panel too.
Thanks a lot!
Alex

 

[NickHK]

Alex,
This updates fine:

Dim Cmdl As String
Cmdl = "rundll32.exe shell32.dll,Control_RunDLL intl.cpl" + ",,5"
Shell Cmdl$, vbNormalFocus

Maybe Tom's reply to the recent thread "Temporarily modify international
options" will help.

NickHK