easy way to check for forbidden characters?

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?

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

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

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?