easy way to check for forbidden characters?

[Ouka]

Hi all,

Is there an easy way to check for forbidden windows characters in a
script that takes the output of a textbox to use in a save as script?


Code:
--------------------
Private Sub CommandButton1_Click()
Dim studyName As String
studyName = Trim(txbStudyName.Value)
ActiveWorkbook.SaveAs Filename:=studyName & ".xls"
Unload Me
End Sub

--------------------


where txbStudyName is the name of a textbox in a user form and I want
to check studyName for any of the forbidden windows filename
characters.

Only way I've managed to do it is like so:


Code:
--------------------
Private Sub CommandButton1_Click()
Dim studyName As String
Dim badChar1 As String
studyName = Trim(txbStudyName.Value)

badChar1 = InStr(1, studyName, "<")

If badChar1 <> "" Then
MsgBox "Study name is invalid."
Else
ActiveWorkbook.SaveAs Filename:=studyName & ".xls"
Unload Me
End If
End Sub

--------------------


That just looks for a "<" character, and if found, creates an output.
Then if there is an output, throw a warning. I've had to make a
separate variable and elseif statement for each character, which makes
for rather cumbersome code. So I was wondering if there was a built-in
feature that would do this for me with just a line or two of code.

While I'm at is, is there a way to make VBA check the save file path
for any files with the same name before trying to save? i.e. if there
is already a file called "Study101.xls" in the destination path, have
VBA code throw the error instead of excel?

Thanks for yer help

--Ouka

 

[Executor]

Hi Ouka,

You could try something like:

Private Sub CommandButton1_Click()
Dim studyName As String
Dim testChar1 As String
Dim iLoop As Integer
Dim iChar As Integer
Dim bNameOk As Boolean

studyName = Trim(txbStudyName.Value)

bNameOk = True
For iLoop = 1 To Len(studyName)
testChar1 = Mid$(studyName, iLoop, 1)
iChar = Asc(testChar1)
If iChar = 46 Or iChar = 95 Then
' test for "." or "_"
ElseIf iChar >= 48 And iChar <= 57 Then
' test for "0" upto "9"
ElseIf iChar >= 65 And iChar <= 90 Then
' test for "A" upto "Z"
ElseIf iChar >= 97 And iChar <= 122 Then
' test for "a' unto "z"
Else
bNameOk = False
Exit For
End If
Next

If bNameOk <> "" Then
MsgBox "Study name is invalid."
Else
ActiveWorkbook.SaveAs Filename:=studyName & ".xls"
Unload Me
End If
End Sub

 

[Ouka]

Thanks, that set me on the right path. Made a couple of alterations and
it works like a charm. Totally forgot about the asci number ranges
*duh*

So, anyone know if there is a way to do the second part of my question?
To make VBA code check for the existance of identical file names in the
target windows folder before the saveas routine fires off and excel
itself returns the error?