Cleaning File Name Before Save

business_analyst

Board Regular
Joined
Jun 5, 2009
Messages
99
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:

Code:
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,617
Try something like this...

Code:
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,132,679
Messages
5,654,712
Members
418,149
Latest member
tjanok

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top