Hi!
I'm reading in a CSV file. I want to use one of the fields as the
name of the WorkSheet. The file is from different clients and the
field is free text. I've come across names with ** or \. Do I have
to use the SUBSTITUTE function 6 times or is there a replaceText
function?
Thanks,
Mechi

Look in VBA help for : Replace Function Make count -1 to replace all
possible eplacement.

" wrote:

Hi!
I'm reading in a CSV file. I want to use one of the fields as the
name of the WorkSheet. The file is from different clients and the
field is free text. I've come across names with ** or \. Do I have
to use the SUBSTITUTE function 6 times or is there a replaceText
function?
Thanks,
Mechi

Hi Mechi

Look at Find / Replace on the Edit menu.

Regards,
Per

skrev i meddelelsen
...
Hi!
I'm reading in a CSV file. I want to use one of the fields as the
name of the WorkSheet. The file is from different clients and the
field is free text. I've come across names with ** or \. Do I have
to use the SUBSTITUTE function 6 times or is there a replaceText
function?
Thanks,
Mechi

This is a function I use for this purpose:

Function MakeValidRangeName(ByVal strRangeName As String, _
Optional strAddIfLeadingNumericsOrDots As String
= "_", _
Optional strAddIfTrailingNumerics As String =
"_", _
Optional bRemoveLeadingNumericsAndDots As
Boolean, _
Optional bRemoveTrailingNumerics As Boolean, _
Optional strSpaceReplace As String = "_") As
String

If Len(strRangeName) = 0 Then
MakeValidRangeName = "No_Name_Provided"
Exit Function
End If

'1. not longer than 255 characters, but allow for added leading and/or
trailing "_"
'----------------------------------------------------------------------------------
strRangeName = Left$(strRangeName, 253)

'2. clear all these. Note that the characters \ and . are valid
'--------------------------------------------------------------
strRangeName = ClearCharsFromString(strRangeName,
"!£$%^&*()-+={}[]:;@'~#|<,?/")

'3. to avoid range names starting with numerics or dots (trailing dots are
fine)
'-------------------------------------------------------------------------------
If bRemoveLeadingNumericsAndDots Then
strRangeName = ClearCharsFromString(strRangeName, ".0123456789", False,
True)
Else
If InStr(1, ".0123456789", Left$(strRangeName, 1), vbBinaryCompare) 0
Then
strRangeName = strAddIfLeadingNumericsOrDots & strRangeName
End If
End If

'4. to avoid range names ending with numerics
'--------------------------------------------
If bRemoveTrailingNumerics Then
strRangeName = ClearCharsFromString(strRangeName, "0123456789", False, ,
True)
Else
If InStr(1, "0123456789", Right$(strRangeName, 1), vbBinaryCompare) 0
Then
strRangeName = strRangeName & strAddIfTrailingNumerics
End If
End If

'5. replace spaces with "_" or other specified character
'-------------------------------------------------------
strRangeName = Replace(strRangeName, " ", strSpaceReplace, 1, -1,
vbBinaryCompare)

MakeValidRangeName = strRangeName

End Function

Function ClearCharsFromString(strString As String, _
strChars As String, _
Optional bAll As Boolean = True, _
Optional bLeading As Boolean, _
Optional bTrailing As Boolean) As String

Dim i As Long
Dim strChar As String

ClearCharsFromString = strString

If bAll Then
For i = 1 To Len(strChars)
strChar = Mid$(strChars, i, 1)
If InStr(1, strString, strChar) 0 Then
ClearCharsFromString = Replace(ClearCharsFromString, _
strChar, _
vbNullString, _
1, -1, vbBinaryCompare)
End If
Next i
Else
If bLeading Then
Do While InStr(1, strChars, Left$(ClearCharsFromString, 1), _
vbBinaryCompare) 0
ClearCharsFromString = Right$(ClearCharsFromString, _
Len(ClearCharsFromString) - 1)
Loop
End If
If bTrailing Then
Do While InStr(1, strChars, Right$(ClearCharsFromString, 1), _
vbBinaryCompare) 0
ClearCharsFromString = Left$(ClearCharsFromString, _
Len(ClearCharsFromString) - 1)
Loop
End If
End If

End Function


RBS


wrote in message
...
Hi!
I'm reading in a CSV file. I want to use one of the fields as the
name of the WorkSheet. The file is from different clients and the
field is free text. I've come across names with ** or \. Do I have
to use the SUBSTITUTE function 6 times or is there a replaceText
function?
Thanks,
Mechi

On Jun 4, 10:55*am, "Per Jessen" wrote:
Hi Mechi

Look at Find / Replace on the Edit menu.

Regards,
Per


I'm reading data from a csv file - I have to replace in strings using
VBScript - before I set the Worksheet name to the string.

On Jun 4, 10:46*am, Joel wrote:
Look in VBA help for : Replace Function *Make count -1 to replace all
possible eplacement.


I'll try that
Thanks!

On Jun 4, 2:47*pm, "RB Smissaert"
wrote:
This is a function I use for this purpose:

Function MakeValidRangeName(ByVal strRangeName As String, _
* * * * * * * * * * * * * * Optional strAddIfLeadingNumericsOrDots As String
= "_", _
* * * * * * * * * * * * * * Optional strAddIfTrailingNumerics As String =
"_", _
* * * * * * * * * * * * * * Optional bRemoveLeadingNumericsAndDots As
Boolean, _
* * * * * * * * * * * * * * Optional bRemoveTrailingNumerics As Boolean, _
* * * * * * * * * * * * * * Optional strSpaceReplace As String = "_") As
String

* If Len(strRangeName) = 0 Then
* * MakeValidRangeName = "No_Name_Provided"
* * Exit Function
* End If

* '1. not longer than 255 characters, but allow for added leading and/or
trailing "_"
* '--------------------------------------------------------------------------*--------
* strRangeName = Left$(strRangeName, 253)

* '2. clear all these. Note that the characters \ and . are valid
* '--------------------------------------------------------------
* strRangeName = ClearCharsFromString(strRangeName,
"!£$%^&*()-+={}[]:;@'~#|<,?/")

* '3. to avoid range names starting with numerics or dots (trailing dots are
fine)
* '--------------------------------------------------------------------------*-----
* If bRemoveLeadingNumericsAndDots Then
* * strRangeName = ClearCharsFromString(strRangeName, ".0123456789", False,
True)
* Else
* * If InStr(1, ".0123456789", Left$(strRangeName, 1), vbBinaryCompare) 0
Then
* * * strRangeName = strAddIfLeadingNumericsOrDots & strRangeName
* * End If
* End If

* '4. to avoid range names ending with numerics
* '--------------------------------------------
* If bRemoveTrailingNumerics Then
* * strRangeName = ClearCharsFromString(strRangeName, "0123456789", False, ,
True)
* Else
* * If InStr(1, "0123456789", Right$(strRangeName, 1), vbBinaryCompare) 0
Then
* * * strRangeName = strRangeName & strAddIfTrailingNumerics
* * End If
* End If

* '5. replace spaces with "_" or other specified character
* '-------------------------------------------------------
* strRangeName = Replace(strRangeName, " ", strSpaceReplace, 1, -1,
vbBinaryCompare)

* MakeValidRangeName = strRangeName

End Function

Function ClearCharsFromString(strString As String, _
* * * * * * * * * * * * * * * strChars As String, _
* * * * * * * * * * * * * * * Optional bAll As Boolean = True, _
* * * * * * * * * * * * * * * Optional bLeading As Boolean, _
* * * * * * * * * * * * * * * Optional bTrailing As Boolean) As String

* Dim i As Long
* Dim strChar As String

* ClearCharsFromString = strString

* If bAll Then
* * For i = 1 To Len(strChars)
* * * strChar = Mid$(strChars, i, 1)
* * * If InStr(1, strString, strChar) 0 Then
* * * * ClearCharsFromString = Replace(ClearCharsFromString, _
* * * * * * * * * * * * * * * * * * * *strChar, _
* * * * * * * * * * * * * * * * * * * *vbNullString, _
* * * * * * * * * * * * * * * * * * * *1, -1, vbBinaryCompare)
* * * End If
* * Next i
* Else
* * If bLeading Then
* * * Do While InStr(1, strChars, Left$(ClearCharsFromString, 1), _
* * * * * * * * * * *vbBinaryCompare) 0
* * * * ClearCharsFromString = Right$(ClearCharsFromString, _
* * * * * * * * * * * * * * * * * * * Len(ClearCharsFromString) - 1)
* * * Loop
* * End If
* * If bTrailing Then
* * * Do While InStr(1, strChars, Right$(ClearCharsFromString, 1), _
* * * * * * * * * * *vbBinaryCompare) 0
* * * * ClearCharsFromString = Left$(ClearCharsFromString, _
* * * * * * * * * * * * * * * * * * *Len(ClearCharsFromString) - 1)
* * * Loop
* * End If
* End If

End Function

RBS

wrote in message

...



Hi!
I'm reading in a CSV file. *I want to use one of the fields as the
name of the WorkSheet. *The file is from different clients and the
field is free text. *I've come across names with ** or \. *Do I have
to use the SUBSTITUTE function 6 times or is there a replaceText
function?
Thanks,
Mechi- Hide quoted text -

- Show quoted text -

Since the program takes over an hour, I don't want to insert more
function calls - your code, though is very helpful and I'll save it
for use at other times - Thanks!

I inserted 8 simple lines:
clientName = Trim(clientName)
clientName = Replace(clientName, "*", "#")
clientName = Replace(clientName, "\", "|")
clientName = Replace(clientName, "/", "|")
clientName = Replace(clientName, "?", "!")
clientName = Replace(clientName, "[", "{")
clientName = Replace(clientName, "]", "}")
clientName = Left(clientName, 27) ' leave room for #

On Wed, 4 Jun 2008 01:25:00 -0700 (PDT), wrote:

Hi!
I'm reading in a CSV file. I want to use one of the fields as the
name of the WorkSheet. The file is from different clients and the
field is free text. I've come across names with ** or \. Do I have
to use the SUBSTITUTE function 6 times or is there a replaceText
function?
Thanks,
Mechi

How about using Regular Expressions. You could run something like the
following function on your chosen field:

========================
Function wsNAME(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[\\/:*?<|]+"
wsNAME = re.Replace(str, "")
End Function
==========================

--ron