aExcel_International store information about a database Ms Access Gurus
Home Code

VBA > Excel automation > Get International Setting

VBA function to get a Windows Region setting from Access using Excel's International property.

Quick Jump

Example Code Logic Why Excel? Other methods Download BAS Index values for the Excel application International property SQL to Query for International settings Index by category and international Values for US Reference Backstory Share

Example

I'm getting ready to post a CalendarMaker tool for Access to make monthly calendars by drawing on an Access report. One of the things it does is use whatever language you've set up with Windows for the day headings and month names. It also lets you change which day of the week you want to start with, since some start weeks with Sunday, Monday, or maybe another day. Initially, the combo list is set with English day names. If you're in another country, the list of day names can be in your language.

When the language in the Windows Region settings isn't English, there's a command button you can click to read Windows Region settings. The VBA code calls the Get_Excel_International function to get the ListSeparator (5) and then constructs a value list of day numbers and day names for the combobox RowSource so when you pick the first day for each week, the day names can be in whatever language you're using.

change value list in combo box RowSource

After you read a setting, you might wish to store it so loading is faster next time. You can use a field in a table, or an object property. Here is code to get and set properties.

Goto Top  

Code 

'*************** Code Start *****************************************************  
' module name: mod_aExcel_International_s4p  
'  http://msaccessgurus.com/VBA/Code/aExcel_International.htm  
'-------------------------------------------------------------------------------  
' Purpose  : read Windows Region International settings using Excel  
' Author   : crystal (strive4peace)  
' License  : below code  
' Code List: www.msaccessgurus.com/code.htm  
'-------------------------------------------------------------------------------  
'           module level variables  
'-------------------------------------------------------------------------------  
'dimension Excel Application object  
'LATE BINDING  
Dim oExcel As Object 'Excel.Application 
'EARLY BINDING  
'Dim oExcel As Excel.Application  
'does Excel need to be Quit?  
Dim pBooQuit As Boolean

'-------------------------------------------------------------------------------  
'           ExcelOpen  
'-------------------------------------------------------------------------------  
Private Function ExcelOpen() As Boolean
'180307 strive4peace, 191129  
'Open or Use Excel so it can be used. Set oExcel
'this procedure is Private and will be called if needed
   On Error Resume Next
   ExcelOpen = False
   pBooQuit = False
   Err.Clear
   'use Excel if it is already running  
   Set oExcel = GetObject(, "Excel.Application")
   If Err.Number <> 0 Then
      'not running, open Excel  
      Set oExcel = CreateObject("Excel.Application")
      'Quit Excel when done  
      pBooQuit = True
   End If
   'set return value. True if oExcel is set  
   ExcelOpen = Not (oExcel Is Nothing)
End Function

'-------------------------------------------------------------------------------  
'           ExcelClose  
'-------------------------------------------------------------------------------  
Public Sub ExcelClose()
'quit Excel and release oExcel object variable 
'this procedure is Public and must be explicitely called
   On Error Resume Next
   If pBooQuit Then
      If Not oExcel Is Nothing Then
         oExcel.Quit
      End If
      pBooQuit = False
   End If
   Set oExcel = Nothing
   Debug.Print Now() & ", Excel released"
End Sub

'-------------------------------------------------------------------------------  
'           Get_Excel_International  
'-------------------------------------------------------------------------------  
Function Get_Excel_International( _
   piIndex As Integer _
   , Optional pvDefaultValue As Variant _
   ) As Variant
'strive4peace 180309, 180718, 191122  
   'RETURN  
   ' Windows region setting using Excel  
   'PARAMETERS  
   '  piIndex = the numeric value of a constant  
   'EXAMPLE  
   '  sListSeparator = Get_Excel_International(5, ";")  
   '  sColumnSeparator = Get_Excel_International(14, ",")  
   '  
   'set up error handler  
   On Error GoTo Proc_Err
   
   Dim vValue As Variant
   
   'initialize return value  
   If Not IsNull(pvDefaultValue) Then
      Get_Excel_International = pvDefaultValue
   Else
      Get_Excel_International = Null
   End If
   
   'open Excel if necessary  
   If oExcel Is Nothing Then
      'this is kept open so you can get more than one setting  
      'without having to initialize Excel again  
      If Not ExcelOpen() Then Exit Function
   End If
   'get value for setting  
   vValue = oExcel.International(piIndex)
   Get_Excel_International = vValue

Proc_Exit:
   On Error Resume Next
   Exit Function
  
Proc_Err:
   Resume Proc_Exit
End Function

'-------------------------------------------------------------------------------  
'           launch  
'-------------------------------------------------------------------------------  
Sub launch_Get_Excel_International()
'191129 strive4peace  
'show various international settings  
   'CALLS  
   '  Get_Excel_International  
   Dim sMsg As String
   
   'get CountryCode using Excel international property  
   sMsg = "CountryCode Is " & Get_Excel_International(1)
   
   '24HourClock, 33  
   sMsg = sMsg & vbCrLf _
      & "Clock Is " & _
      IIf(CBool(Get_Excel_International(33)), "24", "12") _
      & "-hour clock"

   'ListSeparator, 5  
   sMsg = sMsg & vbCrLf _
      & "ListSeparator is" & Get_Excel_International(5)

   Debug.Print sMsg
   MsgBox sMsg, , "International Settings"

   'close Excel (quit and release)  
   Call ExcelClose
      
End Sub

'   You may freely use and share this code  
'     provided this license notice and comment lines are not changed;  
'     code may be modified provided you clearly note your changes.  
'   You may not sell this code alone, or as part of a collection,  
'     without my handwritten permission.  
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.msaccessgurus.com  
'*************** Code End *******************************************************

Goto Top  

Logic

When Get_Excel_International is called, if an Excel object variable (reference) needs to be set (oExcel Is Nothing), ExcelOpen is called. If Excel is already open, that reference is used so loading is faster. If Excel isn't open, a new instance is created. Then the Excel application International property is read for the passed index number. That value is assigned as the function return value. Read however many values you want. When done, call ExcelClose to quit Excel if you started it, and release the Excel reference.

This module has three procedures: one to open Excel, one to close Excel, and one to read an international setting, given an index. It is done this way so you can initialize Excel and then get however many settings you want before releasing Excel.

ExcelOpen

create or use an existing Excel application and set module level object variable, oExcel, so it can be used multiple times

ExcelClose

Quit Excel if it was started, and release the oExcel Excel application object variable.

Get_Excel_International

Return a Windows region setting given an index number. A list of index numbers for the Excel application International property is below.

Parameter for Get_Excel_International

piIndex = the numeric value you want the setting for. Numbers between 1 and 45 are documented. Refer to one of the lists below for the number you want to use.

Goto Top  

Why use Excel?

Excel is a comfort zone for a lot of developers and power-users. Using it to get a single setting is a lot of extra overhead, and not very efficient. Never-the-less, it is a way.

As Branislav pointed out, it is slow and not without problems. If you see this dialog box:

Application Busy, Switch to, Retry, or Cancel

you can switch to Excel and get out of the formula bar, or dialog box, or whatever is preventing Access from using Excel. Once the problem is corrected, the code running in Access can finish.

Other Methods

You can get international settings using other methods. See Daniel Pineault's blog article on devhut, "Special Characters and Internationalization of an Application"

Goto Top  

Download

Click HERE to download the zipped BAS file to get International settings using automation with Excel. This can be run from Access, or any Microsoft Office application usng VBA. It can run from Excel too -- but oExcel would simply be Application, and you wouldn't use OpenExcel or CloseExcel.
(2 kb, unzips to a module BAS file)  

Goto Top  

List of Index values for the
Excel application International property

by index


index   Setting Name   Constant Name Data Type   Category
1 CountryCode xlCountryCode Long Country / Region Settings
2 CountrySetting xlCountrySetting Long Country / Region Settings
3 DecimalSeparator xlDecimalSeparator String Separators
4 ThousandsSeparator xlThousandsSeparator String Separators
5 ListSeparator xlListSeparator String Separators
6 UpperCaseRowLetter xlUpperCaseRowLetter String Brackets and Braces
7 UpperCaseColumnLetter xlUpperCaseColumnLetter String Brackets and Braces
8 LowerCaseRowLetter xlLowerCaseRowLetter String Brackets and Braces
9 LowerCaseColumnLetter xlLowerCaseColumnLetter String Brackets and Braces
10 LeftBracket xlLeftBracket String Brackets and Braces
11 RightBracket xlRightBracket String Brackets and Braces
12 LeftBrace xlLeftBrace String Brackets and Braces
13 RightBrace xlRightBrace String Brackets and Braces
14 ColumnSeparator xlColumnSeparator String Separators
15 RowSeparator xlRowSeparator String Separators
16 AlternateArraySeparator xlAlternateArraySeparator String Separators
17 DateSeparator xlDateSeparator String Date and Time
18 TimeSeparator xlTimeSeparator String Date and Time
19 YearCode xlYearCode String Date and Time
20 MonthCode xlMonthCode String Date and Time
21 DayCode xlDayCode String Date and Time
22 HourCode xlHourCode String Date and Time
23 MinuteCode xlMinuteCode String Date and Time
24 SecondCode xlSecondCode String Date and Time
25 CurrencyCode xlCurrencyCode String Currency
26 GeneralFormatName xlGeneralFormatName String Country / Region Settings
27 CurrencyDigits xlCurrencyDigits Long Currency
28 CurrencyNegative xlCurrencyNegative Long Currency
29 NoncurrencyDigits xlNoncurrencyDigits Long Currency
30 MonthNameChars xlMonthNameChars Long Date and Time
31 WeekdayNameChars xlWeekdayNameChars Long Date and Time
32 DateOrder xlDateOrder Long Date and Time
33 24HourClock xl24HourClock Boolean Date and Time
34 NonEnglishFunctions xlNonEnglishFunctions Boolean Measurement Systems
35 Metric xlMetric Boolean Measurement Systems
36 CurrencySpaceBefore xlCurrencySpaceBefore Boolean Currency
37 CurrencyBefore xlCurrencyBefore Boolean Currency
38 CurrencyMinusSign xlCurrencyMinusSign Boolean Currency
39 CurrencyTrailingZeros xlCurrencyTrailingZeros Boolean Currency
40 CurrencyLeadingZeros xlCurrencyLeadingZeros Boolean Currency
41 MonthLeadingZero xlMonthLeadingZero Boolean Date and Time
42 DayLeadingZero xlDayLeadingZero Boolean Date and Time
43 4DigitYears xl4DigitYears Boolean Date and Time
44 MDY xlMDY Boolean Date and Time
45 TimeLeadingZero xlTimeLeadingZero Boolean Date and Time

Goto Top  

SQL to Query for International settings

Using a Numberz table with sequential Num field, you can get a list of your international settings using the Get_Excel_International function by making a query in Access with this SQL Statement: 

      SELECT Numberz.Num AS idx
      , Get_Excel_International([num]) AS Valu
      FROM Numberz
      WHERE ( Numberz.Num Between 1 And 45 )
      ORDER BY Numberz.Num;

Goto Top  

International US Values

Here is a list of index with US value by category and setting name that shows English (United States) values gotten by using the query above :

by Category

Category   Setting Name   index   Value, US
Brackets and BracesLeftBrace12{
Brackets and BracesLeftBracket10[
Brackets and BracesLowerCaseColumnLetter9c
Brackets and BracesLowerCaseRowLetter8r
Brackets and BracesRightBrace13}
Brackets and BracesRightBracket11]
Brackets and BracesUpperCaseColumnLetter7C
Brackets and BracesUpperCaseRowLetter6R
Country / Region SettingsCountryCode11
Country / Region SettingsCountrySetting21
Country / Region SettingsGeneralFormatName26General
CurrencyCurrencyBefore37-1
CurrencyCurrencyCode25$
CurrencyCurrencyDigits272
CurrencyCurrencyLeadingZeros40-1
CurrencyCurrencyMinusSign380
CurrencyCurrencyNegative280
CurrencyCurrencySpaceBefore360
CurrencyCurrencyTrailingZeros39-1
CurrencyNoncurrencyDigits292
Date and Time24HourClock330
Date and Time4DigitYears43-1
Date and TimeDateOrder320
Date and TimeDateSeparator17/
Date and TimeDayCode21d
Date and TimeDayLeadingZero420
Date and TimeHourCode22h
Date and TimeMDY44-1
Date and TimeMinuteCode23m
Date and TimeMonthCode20m
Date and TimeMonthLeadingZero410
Date and TimeMonthNameChars303
Date and TimeSecondCode24s
Date and TimeTimeLeadingZero450
Date and TimeTimeSeparator18:
Date and TimeWeekdayNameChars313
Date and TimeYearCode19y
Measurement SystemsMetric350
Measurement SystemsNonEnglishFunctions340
SeparatorsAlternateArraySeparator16@
SeparatorsColumnSeparator14,
SeparatorsDecimalSeparator3.
SeparatorsListSeparator5,
SeparatorsRowSeparator15;
SeparatorsThousandsSeparator4,

Goto Top  

Reference

Application.International property (Excel)

Docs / Office VBA Reference / Excel / Object model / Application object / Properties / International

Help: International property

CreateObject function

Docs / Office VBA Reference / Language reference / Reference / Functions / CreateObject

Help: CreateObject

Goto Top  

Backstory

Access is powerful, but can't do everything. Fortunately, Access works well with other Microsoft Office Applications to do what needs to be done.

It seems like there should be a way to get settings with Access, nice that Excel can step in ~

Goto Top  

Share with others

here's the link to copy:
http://msaccessgurus.com/VBA/Code/aExcel_International.htm

Share your comments and code!

Let's communicate, collaborate, and appreciate ... we all get better by sharing. Email me anytime at info@msAccessGurus.com. I enjoy hearing from Access users and developers.

Do you need help?

Do you have a project that could benefit from an expert developer helping you? Let's connect and build your application together. I love teaching and developing, and look forward to hearing from you. ~ crystal

Goto Top