-
Mar 11th, 2019, 05:50 AM
#1
Thread Starter
New Member
Change regional settings from polish to US windows 10
hi Guys,
i am looking for solution to change regional settings using VB in windows 10.
I am trying to use code below:
Code:
#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
-
Mar 11th, 2019, 07:09 AM
#2
Re: Change regional settings from polish to US windows 10
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?
-
Mar 11th, 2019, 07:13 AM
#3
Thread Starter
New Member
Re: Change regional settings from polish to US windows 10
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
-
Mar 11th, 2019, 07:13 AM
#4
Re: Change regional settings from polish to US windows 10
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
Code:
[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>
Last edited by wqweto; Mar 11th, 2019 at 07:17 AM.
-
Mar 11th, 2019, 07:22 AM
#5
Thread Starter
New Member
Re: Change regional settings from polish to US windows 10
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
-
Mar 11th, 2019, 07:23 AM
#6
Thread Starter
New Member
Re: Change regional settings from polish to US windows 10
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
Last edited by jaryszek; Mar 11th, 2019 at 07:34 AM.
-
Mar 11th, 2019, 08:14 AM
#7
Re: Change regional settings from polish to US windows 10
Try putting both sections in schema.ini like this
Code:
[input.csv]
Format=Delimited(,)
MaxScanRows=0
CharacterSet=Unicode
[servers.csv]
Format=Delimited(,)
MaxScanRows=0
CharacterSet=65001
Note: Charset 65001 is utf-8
cheers,
</wqw>
-
Mar 11th, 2019, 08:26 AM
#8
Thread Starter
New Member
Re: Change regional settings from polish to US windows 10
Ok thank you,
and to read this i should use adodb object and sql?
Best,
Jacek
-
Mar 11th, 2019, 08:30 AM
#9
Re: Change regional settings from polish to US windows 10
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>
-
Mar 11th, 2019, 10:56 AM
#10
Re: Change regional settings from polish to US windows 10
Hi,
just convert the data, here a sample
Code:
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
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Mar 11th, 2019, 11:12 AM
#11
Thread Starter
New Member
Re: Change regional settings from polish to US windows 10
Thank you wqweto , very nice!
and thank you ChrisE.
i think that ADO something like this code:
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
-
Mar 11th, 2019, 12:06 PM
#12
Re: Change regional settings from polish to US windows 10
Originally Posted by 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>
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
Code:
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;;;
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Mar 11th, 2019, 12:11 PM
#13
Re: Change regional settings from polish to US windows 10
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>
-
Mar 11th, 2019, 12:21 PM
#14
Re: Change regional settings from polish to US windows 10
Originally Posted by 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>
that fixed it, strange my Sheet names you see from my Debug output ...Tabelle1 ; Tester ; Xcsv
anyway thanks
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Mar 11th, 2019, 01:30 PM
#15
Re: Change regional settings from polish to US windows 10
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:
Code:
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
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Mar 11th, 2019, 02:23 PM
#16
Re: Change regional settings from polish to US windows 10
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>
-
Mar 11th, 2019, 03:17 PM
#17
Re: Change regional settings from polish to US windows 10
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.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Mar 12th, 2019, 01:10 AM
#18
Thread Starter
New Member
Re: Change regional settings from polish to US windows 10
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
-
Mar 12th, 2019, 05:58 AM
#19
Re: Change regional settings from polish to US windows 10aa
@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>
-
Mar 12th, 2019, 06:01 AM
#20
Thread Starter
New Member
Re: Change regional settings from polish to US windows 10
Yes, it is true.
so use code above and not use ReadFromExcel method.
Best,
Jacek
-
Aug 5th, 2020, 04:53 PM
#21
Addicted Member
Re: Change regional settings from polish to US windows 10
Originally Posted by 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:
Code:
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|