Change the system Region/Location setting using vbs

Question

I was wondering if perhaps somebody could assist with a query I have.

Once a week i get file from my US counterpart that i need to save locally to my machine, and change the format of the file. I have since created a VBScript that can perform a SaveAs routine and save it, however in order to retain the date formats in US, I am having to change my Region setting to US in control panel before the procedure and after to change to default local setting.

I have been researching this and got some ideas from the Microsoft site that shows how to use the Set Locale and Get locale, but my code doesnt seem to actually change anything, even though it executes without error.

Can somebody please give me some pointers...

option explicit
dim currentLocale
currentLocale = GetLocale()

SetLocale 1033       ' 1033 is the EN-US locale

' Revert back to the default system locale
SetLocale currentLocale

Unless you're storing formatted strings instead of actual datetime values in the workbook Excel should use the format defined in the system's regional settings for *displaying* the value. It doesn't actually change how the data is stored. Why do you think you need to do this? — Ansgar Wiechers, Sep 25 '15 at 17:22
If you look at the available preset date formats in Excel you will notice a couple at the top that have an asterisk (e.g. `*`) as a prefix character. *"Date formats that begin with an asterisk respond to changes in regional date and time settings that are specified for the operating system".* — , Sep 26 '15 at 17:10
@Ansgar Wiechers Basically what happens is, i get the file from a US counterpart, which is exported as excel, but the actual file is html, as this needs to feed a SQL server import (again in the US), i have to do a saveas .xls on the file, and then import it into the SQL server. The server expects the dates to be in US format. As I am based in the UK, excel changes the format of the date from mm/dd to dd/mm. Now thanks to you, i can run a vbs to saveas the file, but i was hoping to incorporate the system region change in there too. Otherwise using the VBS to saveas xls is of no use. — Saf, Jan 20 '16 at 15:10

score 2 · Answer 1 · answered Jan 27 '16 at 13:38

Option Explicit

'Define a key registry path
Dim strComputer
Dim objRegistry
Dim strKeyPath
Dim strValueName
Dim getValue
Dim regKeyPath
Dim strLocaleName, strCountry, strshortDateValue, strlongDateValue, strshortTimeValue, strlongTimeValue, strfirstDayOfWeekValue

Const HKEY_CURRENT_USER = &H80000001
strComputer = "."
Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
regKeyPath = "Control Panel\International"

strLocaleName = "en-US" 
strCountry = "United States" 
strshortDateValue = "M/d/yyyy"
strlongDateValue = "dddd, MMMM d, yyyy"
strshortTimeValue = "h:mm tt"
strlongTimeValue = "h:mm:ss tt"
strfirstDayOfWeekValue = "6"

objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "LocaleName", strLocaleName
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sCountry", strCountry
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sShortDate", strshortDateValue
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sLongDate", strlongDateValue
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sShortTime", strshortTimeValue
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sTimeFormat", strlongTimeValue
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "iFirstDayOfWeek", strfirstDayOfWeekValue


WScript.Echo "Successfully changed system regional settings."

This script will change your regional settings in registry. Use with caution. This will change to US, so if you intend on using this script, make sure you know what the default settings are before you change them. Merely reverting the settings in Control Panel will not work.

The script below will change everything back to UK again.

Option Explicit

'Define a key registry path
Dim strComputer
Dim objRegistry
Dim strKeyPath
Dim strValueName
Dim getValue
Dim regKeyPath
Dim strLocaleName, strCountry, strshortDateValue, strlongDateValue, strshortTimeValue, strlongTimeValue, strfirstDayOfWeekValue

Const HKEY_CURRENT_USER = &H80000001
strComputer = "."
Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
regKeyPath = "Control Panel\International"

strLocaleName = "en-GB" 
strCountry = "United Kingdom" 
strshortDateValue = "dd/MM/yyyy"
strlongDateValue = "dd MMMM yyyy"
strshortTimeValue = "HH:mm"
strlongTimeValue = "HH:mm:ss"
strfirstDayOfWeekValue = "0"

objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "LocaleName", strLocaleName
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sCountry", strCountry
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sShortDate", strshortDateValue
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sLongDate", strlongDateValue
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sShortTime", strshortTimeValue
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "sTimeFormat", strlongTimeValue
objRegistry.SetStringValue HKEY_CURRENT_USER, regKeyPath, "iFirstDayOfWeek", strfirstDayOfWeekValue


WScript.Echo "Successfully changed system regional settings."

Change the system Region/Location setting using vbs

1 Answers1

Linked