Get list separator character with VBA

[Luc Benninger]

Hi
Anyone knows a way to get windows' list separator character with vba in
pp xp/2003? Is there a registry key I can read?
Thanks for any hints
Luc

 

[Steve Rindsberg]

Hi
Anyone knows a way to get windows' list separator character with vba in
pp xp/2003? Is there a registry key I can read?

Which list separator character are you after?

Windows uses Null - Chr$(0) or VBNull - to separate filenames in lists returned
from the file dialog box, for example.

Is that what you needed?

 

[Luc Benninger]

No. I meant the list separator you can define in the regional options in
the control panel. In Word and Excel, there is a VBA property which
returns this separator character. But not in PowerPoint.

 

[Shyam Pillai]

Luc,

'------------------------------------------------
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 GetUserDefaultLCID% Lib "kernel32" ()

Public Const LOCALE_SLIST = &HC


Public Sub GetListSeparator()
Dim ListSeparator As String
Dim iRetVal1 As Long
Dim iRetVal2 As Long
Dim lpLCDataVar As String

Dim Position As Integer
Dim Locale As Long

Locale = GetUserDefaultLCID()

iRetVal1 = GetLocaleInfo(Locale, LOCALE_SLIST, lpLCDataVar, 0)

ListSeparator = String$(iRetVal1, 0)

iRetVal2 = GetLocaleInfo(Locale, LOCALE_SLIST, ListSeparator,
iRetVal1)

Position = InStr(ListSeparator, Chr$(0))
If Position > 0 Then
ListSeparator = Left$(ListSeparator, Position - 1)
MsgBox "List separator is = " + ListSeparator
End If

End Sub

'-----------------------------------------------------------------

 

[Luc Benninger]

Great! Thank you very much Shyam.

 

[Steve Rindsberg]

No. I meant the list separator you can define in the regional options in
the control panel. In Word and Excel, there is a VBA property which
returns this separator character. But not in PowerPoint.

HKEY_CURRENT_USER\Control Panel\International
The value's in the key: sList

(I just changed it to a ridiculous separator -- @#$ --- then searched the registry
for that set of characters.)

Shyam's code will probably be more reliable over a range of systems, though.

 

[sisyphus]

Application.International(wdListSeparator) is Read/Write
For Excel use xlListSeparator. Unfortunately, the two values aren't the same (Office 2003)