Remove all restricted characters from save file name

[mweick]

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:

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!

 

[Andrew Poulsom]

Here's a function that will return a valid filename from its argument:

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

 

[mweick]

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?

 

[jagadeesh.rt]

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.


[TABLE="width: 205"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Dinesh Korrapathi[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]P.Parusuram[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Naga Lakshmi[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[par60056]

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.

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

 

[Andrew Poulsom]

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:

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

Then strFilename will contain a valid name.

 

[Andrew Poulsom]

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.


[TABLE="width: 205"]
<TBODY>[TR]
[TD]Dinesh Korrapathi
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]P.Parusuram
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Naga Lakshmi
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY>[/TABLE]

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.

 

[mweick]

Worked Great!

thanks for all your help Andrew and Par66056!

 

[Rick Rothstein]

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)...

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

 

[Abdullah AGAR]

Hi Andrew,


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

Could you please help me out...

Thanks in advance....

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