Remove all restricted characters from save file name

mweick

New Member
Joined
Nov 9, 2012
Messages
22
Hello,

On my spreadsheet we have a submit that users click after they enter all their information and that initiates an excel macro I wrote to save the file to a certain location. Here is the code I am currently using:

Code:
Sub SaveFile()
Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next ' If directory exist goto next line
strDirname = Range("ED1").Value ' New directory name

strFilename = Range("B11").Value 'New file name
strDefpath = "S:\" 'Default path name
If IsEmpty(strDirname) Then Exit Sub
If IsEmpty(strFilename) Then Exit Sub

MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "\" & strFilename 'create total string

ActiveWorkbook.SaveAs Filename:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

As you can see, the file name is saved off of whatever is entered in cell B11. The problem is, sometimes users have to enter characters such as \ / : * ? " < > |into cell B11 and then when they hit the submit button it gives them a confirmation that the file saved successfully even though it didn't because there were one of the characters mentioned above in cell B11.

I tried to figure this out but I can't. I need some code to be written in this macro to still allow the entering of those characters in cell B11 but when they click submit, I need the macro to strip all of the characters above just for the save file name while keeping the characters physically in cell B11.

Any help would be greatly appreciated!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Here's a function that will return a valid filename from its argument:

Code:
Private Function ValidWBName(Arg As String) As String
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Pattern = "[\\/:\*\?""<>\|]"
        .Global = True
        ValidWBName = .Replace(Arg, "")
    End With
End Function
 
Upvote 0
Thanks for the quick response Andrew!

I implemented this code into my workbook but it didn't work...

This macro that you wrote I believe has to be merged with my original save file macro, I could be wrong but I don't think your macro is communicating with my macro and that's why it's not stripping those characters. Maybe?
 
Upvote 0
Hi,

Please help me conditional formatting for the below results. When cell is zero should not displayed/blank cell, if more than zero should be reported.


Dinesh Korrapathi13100
P.Parusuram13000
Naga Lakshmi00000

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>
 
Upvote 0
Did you call his function?

ActiveWorkbook.SaveAs Filename:=strPathname, _

should become

ActiveWorkbook.SaveAs Filename:=ValidWBName(strPathname), _

His function is very clean but you have to have other references available and it will not work for Mac Excel.

I wrote my own function a long time ago.

Code:
Function FixWBFileName(FileName As String) As String
Dim fname As String


fname = Trim(FileName)


fname = Replace(fname, " ", "_")
fname = Replace(fname, ",", "")
fname = Replace(fname, "'", "")
fname = Replace(fname, "(", "")
fname = Replace(fname, ")", "")
fname = Replace(fname, "~", "")
fname = Replace(fname, "*", "")
fname = Replace(fname, "?", "")
fname = Replace(fname, "/", "")
fname = Replace(fname, "\", "")
fname = Replace(fname, """", "")


FixWBFileName = fname


End Function
 
Upvote 0
Thanks for the quick response Andrew!

I implemented this code into my workbook but it didn't work...

This macro that you wrote I believe has to be merged with my original save file macro, I could be wrong but I don't think your macro is communicating with my macro and that's why it's not stripping those characters. Maybe?

You need to add the function to your module and pass it the filename like this:

Code:
strFilename = ValidWBName(Range("B11").Value)

Then strFilename will contain a valid name.
 
Upvote 0
Hi,

Please help me conditional formatting for the below results. When cell is zero should not displayed/blank cell, if more than zero should be reported.


Dinesh Korrapathi
1
3
1
P.Parusuram
1
3
Naga Lakshmi

<TBODY>
</TBODY>

I don't know why you didn't start your own thread, but you can suppress the display of zeroes with a number format like:

General;General;

The key is the trailing semicolon.
 
Upvote 0
Using the information here... Error Message: Filename is Invalid or Cannot Contain Any of the Following Characters... this is the function I developed a while ago (should work on both PC's and Mac's both)...
Code:
Function MakeValidFileName(ByVal FileName As String) As String
  Dim X As Long
  For X = 1 To Len(FileName)
    If Mid(FileName, X, 1) = "]" Or Mid(FileName, X, 1) Like _
       "[! 0-9A-Za-z^&'@{}[,$=!#()%.+~_-]" Then Mid(FileName, X, 1) = "*"
  Next
  MakeValidFileName = Replace(FileName, "*", "")
End Function
 
Upvote 0
Hi Andrew,


I have the same problem with saving / * - characters in my excel (2013) filename.

Could you please help me out...

Thanks in advance....


Code:
Private Sub CommandButton1_Click()
 
Dim Filename As String
Dim Path As String


Application.DisplayAlerts = False


Path = "Z:\data\ihracat\D_YURTiCi\Siparis ve Satin Alma"
Filename = Range("i10").Value & ".xlsx"


ActiveWorkbook.SaveAs Path & Filename, xlOpenXMLWorkbook


Application.DisplayAlerts = True


ActiveWorkbook.Close


End Sub
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top