Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do i modify the List Seperator Character

Posted on 2006-07-24
6
Medium Priority
?
3,148 Views
Last Modified: 2008-01-09
I have a question regarding the regional settings on a PC.

I have two applications, one is an Excel workbook with lots of VBA and another is a Word document also with lots of VBA.

The Word document will only work properly when the LIST SEPARATOR is set to ;
The Excel sheet will only work properly when the LIST SEPARATOR is set to ,

This is set via:

Start
Control Panel
Regional & Language Options
Regional Options Tab
Customize
List Separator  , or ; depending on application.

Its not possible to modify things to make it work with a general list separator... tried that earlier ... see

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21817252.html


However, my question is, is there any way of setting the regional settings via VBA when the sheet opens so that an error does not occur ?

Alternatively can i check the value of the List Seperator and if its not a ; as required then use a pop up box to issue a warning to the user.

Thanks

Gordon
0
Comment
Question by:GordonMasson
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17169793
HI Gordon.
Where, in Word, does the list separator need to be a ";"?
0
 

Author Comment

by:GordonMasson
ID: 17170107
Hi Graham

Its a historic document that i cant really change... i dont think???

code is to do with inserting a table of contents at a bookmark ...TOC

Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""

I assumee that changing this to:
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
would fix the problem????? b ut im not sure if there is anything else in the code that uses this.... is it only formulas that use the list seperator ?

perhaps i can do somthing like ...... (using syntax that detects the list seperator

If ListSeperator = ";" Then
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""
Else
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
End If

Thanks  Gordon
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 600 total points
ID: 17170897
I have done a few quick tests (Word 2003), and I think that the TOC field, while being sensitve to the List separator setting, seems to work as  one would expect.
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 

Author Comment

by:GordonMasson
ID: 17173933
Sorry Graham ... not with you ... are you saying that the code below will work (or some varient of that which has the correct syntax)....

If ListSeperator = ";" Then
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""
Else
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
End If


I think this is the safest way for me to go so that the code would detect how the List Seperator is set up on the PC (some pc's that use this have it set as ' others as ;) and then run the line of code which suits the application / PC.

If this is correct can you tell mw what the code would be to detect the PC's List Seperator setting in VBA?

Thanks

gordon
0
 
LVL 9

Accepted Solution

by:
dmang earned 1400 total points
ID: 17176269
Found a sample on the net that was originally taken from winapi.txt, and tweaked it some!

You can retrieve and set regional settings using the following APIs....

Declare Function GetLocaleInfo Lib "kernel32" Alias _
"GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String, ByVal cchData As Long) As Long

Declare Function SetLocaleInfo Lib "kernel32" Alias _
"SetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String) As Boolean

Declare Function GetUserDefaultLCID% Lib "kernel32" ()


'parm value
Public Const LOCALE_SLIST = &HC

'Subs to retireve / set:

Public Sub Get_locale() ' Retrieve the regional setting

      Dim Symbol As String
      Dim iRet1 As Long
      Dim iRet2 As Long
      Dim lpLCDataVar As String
      Dim Pos As Integer
      Dim Locale As Long
     
      Locale = GetUserDefaultLCID()
     
      iRet1 = GetLocaleInfo(Locale, LOCALE_SLIST, _
      lpLCDataVar, 0)
      Symbol = String$(iRet1, 0)
     
      iRet2 = GetLocaleInfo(Locale, LOCALE_SLIST, Symbol, iRet1)
      Pos = InStr(Symbol, Chr$(0))
      If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           MsgBox "Regional Setting = " + Symbol
      End If

End Sub

Public Sub Set_locale() 'Change the regional setting

      Dim Symbol As String
      Dim iRet As Long
      Dim Locale As Long      

      Locale = GetUserDefaultLCID() 'Get user Locale ID
      Symbol = "-" 'New character for the locale
      iRet = SetLocaleInfo(Locale, LOCALE_SLIST, Symbol)
     
End Sub




0
 

Author Comment

by:GordonMasson
ID: 17190510
Hi folks

I got this working using this....

Dim fld As Field
If ActiveDocument.Bookmarks.Exists("TOCApp") = True Then
       
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldTOC Then
            If InStr(fld.Code.Text, "SUBAPPENDIX") Then
                fld.Code.Text = "TOC \F \N \T " & "APPENDIX" & Application.International(wdListSeparator) & "1" & Application.International(wdListSeparator) & "SUBAPPENDIX" & Application.International(wdListSeparator) & "2"
                Exit For
            End If
        End If
    Next fld
   
End If


Gets me round the problem of PC's with different List Separators set.

i have split the points...thanks for your help...

Gordon
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Suggested Courses
Course of the Month7 days, 10 hours left to enroll

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question