Solved

How do i modify the List Seperator Character

Posted on 2006-07-24
6
2,776 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
Comment Utility
HI Gordon.
Where, in Word, does the list separator need to be a ";"?
0
 

Author Comment

by:GordonMasson
Comment Utility
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 150 total points
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:GordonMasson
Comment Utility
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 350 total points
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction Authors who set out to write any sort of lengthy piece for online submission—be it a long question or comment on a technical form, an article, or a substantial blog entry—often find it useful to work up a draft in an editor other t…
I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now