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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,243
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
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).
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,506
Messages
5,511,706
Members
408,862
Latest member
sidneybc

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top