Cleaning File Name Before Save

[business_analyst]

Hello All,

I am using the GetSaveAsFileName function in order to save an excel file through a user form. This function works perfectly within a Userform that prompts a user to to enter a name for the file, then click a button to save as that name. The issue I have come across is one I probably should have recognized before, but didn't catch it until testing. A user can enter any type of character in this Textbox, which is then auto filled into the input box that pops up from GetSaveasFileName. So if they enter somthing with a "." for instance, or a "/", that is how the file gets saved, which essentially messes up the entire save function. Is there any string function that can clean the text entered before it is used as the filename? To basically remove all character except for alphabet and numbers? Here is the code is have as of right now:

Private Sub cmd_2_save_app_click()
    Dim cust_name As String
    save_location = True
    
    save_timestamp = format(Now(), "yyyy-mm-dd_hhmm")
    
    If txt_01_nm.value = "" Then
        continue = MsgBox("Please enter customer name before attempting to save", vbInformation, "To Save...")
    Else
        cust_name = txt_01_nm.value & "_" & save_timestamp
        save_path = Application.GetSaveAsFilename(cust_name, "Excel Files (*.xls), *.xls", 1, "Save Application To...")
    End If

End Sub

txt_01_nm.value is the value that I want to clean as it is being used to save the file.

 

[Domenic]

Try something like this...

Dim i as Long
Dim Temp as String
Dim FileName as String

FileName = txt_01_nm.value

For i = 1 To Len(FileName)
    If IsError(Application.Match(Mid(FN, i, 1), Array("[", "*", ":", "?", "/", "\", "]"), 0)) Then
        Temp = Temp & Mid(FileName, i, 1)
    End If
Next i

FileName = Temp

...which will remove the following illegal characters...

[
?
*
:
\
/
]

If you'd like to remove ".", include it in the array.

 

[xenou]

This site lists a few more illegal characters - pipes, double-quotes, greater than and less than (but does not list brackets - I think those are okay as I see html temp files with them from time to time):
http://msdn.microsoft.com/en-us/library/aa365247(VS.85).aspx

I used to be a strict A-Z, 0-9 file namer but lately I've started using other characters a bit more (periods, dollar signs, commas, parantheses).