Change regional settings from polish to US windows 10

[jaryszek]

hi Guys,

i am looking for solution to change regional settings using VB in windows 10.

I am trying to use code below:

#If VBA7 Then
    Private Declare PtrSafe Function SetThreadLocale Lib "kernel32" _
        (ByVal Locale As Long) As Boolean
    Private Declare PtrSafe Function GetUserDefaultLCID Lib "kernel32" () As Long
    Private Declare PtrSafe Function LocaleNameToLCID Lib "kernel32" _
        (ByVal lpName As LongPtr, dwFlags As Long) As Long
#Else
    Private Declare Function SetThreadLocale Lib "kernel32" (ByVal Locale As Long) As Boolean
    Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
    Private Declare Function LocaleNameToLCID Lib "kernel32" _
       (ByVal lpName As LongPtr, dwFlags As Long) As Long
#End If

Private Sub Test() 
Dim userLocale As Long 
'US settings number
userLocale = GetUserDefaultLCID 
userLocale = 1033 
SetThreadLocale userLocale 
End Sub

Cross posted here:
https://social.msdn.microsoft.com/Fo...forum=exceldev
and here:
https://www.excelforum.com/excel-pro...e-to-us-2.html

So what i want is to change regional settings from windows default (in my case Polish) to us.
Polish getUserDefaultLCID = 1045, US = 1033.

Why the code is not working? Can anybody use VB script to change it?
Maybe powershell script?

The code is only for my computer and i the solution can be very useful for me only, so not worry - it will be not harmful for other users.

Please help,
Best,
Jacek

[Arnoutdv]

Is there a specific reason why you need to change it in your application/vba code?
Has it to do with text imports in US style?

[jaryszek]

Hi Arnoutdv,

thank you for getting involved.
Yes, exactly. When opening csvs based on US style i have to use text to column all the time. I can have macro to do it but i wnat to try also wiyh changing regional settings.

Jacek

[wqweto]

Let me chime -- this is *not* possible.

Changing regional settings *requires* starting Windows Setup, i.e. this is like reinstalling a particular small part of the operating system.

Edit: Using comma in CSV is way much easier. In the folder where your input.csv is placed just make sure you have a schema.ini file like this

[input.csv]
Format=Delimited(,)
MaxScanRows=0

Using your VBA prowess to generate the schema file would be much easier/less invasive than switching user's regional settings IMO.

cheers,
</wqw>

[jaryszek]

o wow what a great idea wqweto,

Why to generete schema.ini from VBA?
The better option would be creating the file manually and import somehow to Excel?

Best,
Jacek

[jaryszek]

o wow what a great idea wqweto,

Why to generete schema.ini from VBA?
The better option would be creating the file manually and import somehow to Excel?

edit: i could have only schema.ini files for files which i want to open with US settings.
for example Input.csv, servers.csv.
And if file name is like inside schema.ini (maybe i can change also schema.ini name to input.ini name?)
I could import data properly.

Can you give a tip how to do it?
Thank you !

Best,
Jacek

[wqweto]

Try putting both sections in schema.ini like this

[input.csv]
Format=Delimited(,)
MaxScanRows=0
CharacterSet=Unicode

[servers.csv]
Format=Delimited(,)
MaxScanRows=0
CharacterSet=65001

Note: Charset 65001 is utf-8

cheers,
</wqw>

[jaryszek]

Ok thank you,

and to read this i should use adodb object and sql?

Best,
Jacek

[wqweto]

You'll need ADO for sure but not SQL Server.

Take a look at this gist: Write to Excel using ADO

You'll need something like the ReadFromExcel function (which can read XLS, XLSX, XLSB and CSV files) but much simpler.

cheers,
</wqw>

[ChrisE]

Hi,

just convert the data, here a sample

Private Sub Command1_Click()
Text1.Text = CDblGerman(123.15)
End Sub

'Convert String > Double, deutsche Schreibweise,
'unabhängig von Ländereinstellung
Public Function CDblGerman(sString As String) As Double

   Static DecPoint As Byte
   Dim s As String
   
      If DecPoint = 0 Then
         s = Format(0, "0.0")
         DecPoint = Asc(Mid(s, 2, 1))
      End If
         
      'Tausendertrenner entfernen
      s = Replace(sString, ".", vbNullString)
      'Komma umsetzen in Dezimalpunkt (Ländereinstellung
      s = Replace(sString, ",", Chr(DecPoint))
      'Double liefern
      CDblGerman = CDbl(s)
End Function

[jaryszek]

Thank you wqweto , very nice!

and thank you ChrisE.

i think that ADO something like this code:

Sub UpdateSKU()
Dim ws As Worksheet
Dim fPath As String: fPath = "C:\Test\" 'Change as needed
Dim cn As Object, rst As Object

Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & fPath & ";" & _
                        "Extended Properties=""text;HDR=NO;FMT=Delimited;Imex=1;ImportMixedTypes=Text;"""
    .CursorLocation = 3
    .Open
End With

strQuery = "SELECT t2.[F1], t1.[F2], t1.[F3], t1.[F4], t1.[F5] From [Test2.csv] as t1 " & _
            "RIGHT OUTER JOIN [Test1.csv] as t2 " & _
            " ON t2.[F1] = t1.[F1];"

Set rst = CreateObject("ADODB.Recordset")
rst.Open strQuery, cn, 1, 3

Range("A1").CopyFromRecordset rst
rst.Close
cn.Close

End Sub

thank you !

Best Wishes,
Jacek

[ChrisE]

You'll need ADO for sure but not SQL Server.

Take a look at this gist: Write to Excel using ADO

You'll need something like the ReadFromExcel function (which can read XLS, XLSX, XLSB and CSV files) but much simpler.

cheers,
</wqw>

Hi wqw

just tried your ReadFromExcel Funtion and get strange results, some sheets canot be found within a .xlsx and with another sheet
in the same Woorkbook it is written correctly to the Debug window ?
any thoughts why this happens

here my Debug output

Das Microsoft Access-Datenbankmodul konnte das Objekt 'Tester' nicht finden. Stellen Sie sicher, dass das Objekt vorhanden ist und dass die Namens- und Pfadangaben richtig eingegeben wurden. Ist 'Tester' kein lokales Objekt, sollten Sie die Netzwerkverbindung prüfen oder sich an den Serveradministrator wenden. in ReadFromExcel
Das Microsoft Access-Datenbankmodul konnte das Objekt 'Tabelle1' nicht finden. Stellen Sie sicher, dass das Objekt vorhanden ist und dass die Namens- und Pfadangaben richtig eingegeben wurden. Ist 'Tabelle1' kein lokales Objekt, sollten Sie die Netzwerkverbindung prüfen oder sich an den Serveradministrator wenden. in ReadFromExcel
Das Microsoft Access-Datenbankmodul konnte das Objekt 'Xcsv' nicht finden. Stellen Sie sicher, dass das Objekt vorhanden ist und dass die Namens- und Pfadangaben richtig eingegeben wurden. Ist 'Xcsv' kein lokales Objekt, sollten Sie die Netzwerkverbindung prüfen oder sich an den Serveradministrator wenden. in ReadFromExcel
19;New England Seafood Cannery;Robb Merchant;Wholesale Account Agent;Order Processing Dept.
2100 Paul Revere Blvd.;Boston;MA;02134;USA;(617) 555-3267;(617) 555-3389;;
2;New Orleans Cajun Delights;Shelley Burke;Order Administrator;P.O. Box 78934;New Orleans;LA;70117;USA;(100) 555-4822;;#CAJUN.HTM#;
13;Nord-Ost-Fisch Handelsgesellschaft mbH;Sven Petersen;Das Microsoft Access-Datenbankmodul konnte das Objekt 'Tabelle1' nicht finden. Stellen Sie sicher, dass das Objekt vorhanden ist und dass die Namens- und Pfadangaben richtig eingegeben wurden. Ist 'Tabelle1' kein lokales Objekt, sollten Sie die Netzwerkverbindung prüfen oder sich an den Serveradministrator wenden. in ReadFromExcel
Das Microsoft Access-Datenbankmodul konnte das Objekt 'Tabelle1' nicht finden. Stellen Sie sicher, dass das Objekt vorhanden ist und dass die Namens- und Pfadangaben richtig eingegeben wurden. Ist 'Tabelle1' kein lokales Objekt, sollten Sie die Netzwerkverbindung prüfen oder sich an den Serveradministrator wenden. in ReadFromExcel
Das Microsoft Access-Datenbankmodul konnte das Objekt 'Tabelle1' nicht finden. Stellen Sie sicher, dass das Objekt vorhanden ist und dass die Namens- und Pfadangaben richtig eingegeben wurden. Ist 'Tabelle1' kein lokales Objekt, sollten Sie die Netzwerkverbindung prüfen oder sich an den Serveradministrator wenden. in ReadFromExcel
Das Microsoft Access-Datenbankmodul konnte das Objekt 'Tabelle1' nicht finden. Stellen Sie sicher, dass das Objekt vorhanden ist und dass die Namens- und Pfadangaben richtig eingegeben wurden. Ist 'Tabelle1' kein lokales Objekt, sollten Sie die Netzwerkverbindung prüfen oder sich an den Serveradministrator wenden. in ReadFromExcel
Coordinator Foreign Markets;Frahmredder 112a;Cuxhaven;;27478;Germany;(04721) 8713;(04721) 8714;;
15;Norske Meierier;Beate Vileid;Marketing Manager;Hatlevegen 5;Sandvika;;1320;Norway;(0)2-953010;;;

[wqweto]

Try to dump the TABLE_NAME field from cn.OpenSchema(adSchemaTables) as sheet names tend to become a big mess very fast.

Most sheet names end with $ but some have spaces in most strange places e.g. "Sheet1 $".

cheers,
</wqw>

[ChrisE]

Try to dump the TABLE_NAME field from cn.OpenSchema(adSchemaTables) as sheet names tend to become a big mess very fast.

Most sheet names end with $ but some have spaces in most strange places e.g. "Sheet1 $".

cheers,
</wqw>

that fixed it, strange my Sheet names you see from my Debug output ...Tabelle1 ; Tester ; Xcsv

anyway thanks
Chris

[Elroy]

Hey, I'll chime in here too. I've got a similar problem, and I fixed it more specifically. I'm not sure you need to change your entire regional setting to read/write these CSV files. How about just changing the decimal setting? The following is how I deal with it:

Option Explicit
'
Private Declare Function GetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Private Declare Function SetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Long
'

Public Sub ForceSystemDecimalToPeriod()
    ' We MUST use the ANSI API version so it's an ANSI character that's used for the actual decimal character.
    Const LOCALE_SDECIMAL   As Long = &HE&
    Const LOCALE_SGROUPING  As Long = &H10&
    Const Eng_LCID          As Long = 1033&
    Dim s As String
    '
    s = String$(GetLocaleInfoA(Eng_LCID, LOCALE_SDECIMAL, vbNullString, 0&), 0)
    GetLocaleInfoA Eng_LCID, LOCALE_SDECIMAL, s, Len(s)
    If RTrimNull(s) <> "." Then
        SetLocaleInfoA Eng_LCID, LOCALE_SDECIMAL, "."
        SetLocaleInfoA Eng_LCID, LOCALE_SGROUPING, ","
    End If
End Sub

Public Function RTrimNull(s As String) As String
    RTrimNull = s
    Do
        If Right$(RTrimNull, 1) <> vbNullChar Then Exit Do
        RTrimNull = Left$(RTrimNull, Len(RTrimNull) - 1)
    Loop
End Function

And, if you wanted, you might be able to save it, do your CSV work, and then put it back the way it was. My problem is a bit different from reading CSV files, but the concept should be the same.

Good Luck,
Elroy

[wqweto]

Using SetLocaleInfo would be too invasive and the docs says so:

Caution Because this function modifies values for all applications, it should only be called by the regional and language options functionality of Control Panel, or a similar utility. . .

Unfortunately there is no locale configuration function in ole automation (like Delphi's runtime for instance) so I usually have to resort to some pretty crude measures -- hooking GetLocaleInfoA/W for LOCALE_SDECIMAL from import sections of oleaut32.dll and comctl32.dll (and mscomct2.ocx) for my application process.

This has to be done before oleaut caches the locale settings or it will be too late for the hook to have any impact to type converting family of functions -- CDbl mostly but also intrinsicly used VariantChangeType API too.

cheers,
</wqw>

[Elroy]

Yeah, I knew that (that it was a global change). I suppose I should have pointed that out in my above post. In my application, I force it, but my users are aware of that.

However, as I suggested, if you read it, save it, change it, do your work, and then restore it ... that should be okay.

[jaryszek]

Hi Elroy,

thank you. Yes it can be harmful for system but you have to first run your code to change decimals, later you have to run your csv with US settings, do your work, closed it and run once again decimal settings changer macro.
I think this is not the best option here.

ChrisE,
did you solve the problem?
Do not use WriteToExcel function when you want to get schema.ini loaded. Use function above what i provided.

Best,
Jacek

[wqweto]

@jaryszek: You mean *ReadFromExcel*, not WriteToExcel?

Yes, ReadFromExcel overwrites schema.ini so this might be a problem if schema.ini file is created manually.

cheers,
</wqw>

[jaryszek]

Yes, it is true.

so use code above and not use ReadFromExcel method.

Best,
Jacek

[Jimboat]

Hey, I'll chime in here too. I've got a similar problem, and I fixed it more specifically. I'm not sure you need to change your entire regional setting to read/write these CSV files. How about just changing the decimal setting? The following is how I deal with it:

Option Explicit
'
Private Declare Function GetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Private Declare Function SetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Long
'

Public Sub ForceSystemDecimalToPeriod()
    ' We MUST use the ANSI API version so it's an ANSI character that's used for the actual decimal character.
    Const LOCALE_SDECIMAL   As Long = &HE&
    Const LOCALE_SGROUPING  As Long = &H10&
    Const Eng_LCID          As Long = 1033&
    Dim s As String
    '
    s = String$(GetLocaleInfoA(Eng_LCID, LOCALE_SDECIMAL, vbNullString, 0&), 0)
    GetLocaleInfoA Eng_LCID, LOCALE_SDECIMAL, s, Len(s)
    If RTrimNull(s) <> "." Then
        SetLocaleInfoA Eng_LCID, LOCALE_SDECIMAL, "."
        SetLocaleInfoA Eng_LCID, LOCALE_SGROUPING, ","
    End If
End Sub

Public Function RTrimNull(s As String) As String
    RTrimNull = s
    Do
        If Right$(RTrimNull, 1) <> vbNullChar Then Exit Do
        RTrimNull = Left$(RTrimNull, Len(RTrimNull) - 1)
    Loop
End Function

And, if you wanted, you might be able to save it, do your CSV work, and then put it back the way it was. My problem is a bit different from reading CSV files, but the concept should be the same.

Elroy - will this same code work in Win10, Win7 and WinXP also? Does user security settings have any impact on changing LOCALE_SDECIMAL, LOCALE_SGROUPING, LOCALE_STHOUSAND?