Remove illegal characters while saving workbook Excel VBA

Question

this code basically reformats an xls file and saves it as an xlsx. however it uses G2 & H2 to grab the filename for the newly formatted file. So that means certain characters can't be in the file name. I added a chunk of code to replace those characters (

' Remove/Replace Invalid File Name Characters
 WkbName = Range("H2")
    MyArray = Array("<", ">", "|", "/", "*", "\", ".", "?", """")
    For X = LBound(MyArray) To UBound(MyArray)
        WkbName = Replace(WkbName, MyArray(X), "_", 1)
            Next X
                'MsgBox WkbName     'dispaly file name with illegal characters removed

    ActiveWorkbook.SaveAs Filename:= _
       WBPath & "\BOM_" & Range("G2") & "_" & WkbName & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 

) activeworkbook.saves as is where the debugger always takes me

I'm getting an error message saying there's always an illegal character even if its just normal text in h2, am I missing something?

full code below

Sub FormatBOMExport()
'
' FormatBOMExportPnV Macro
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False

' delete extra sheets
Sheets(Array("Sheet2", "Sheet3")).Select
    ActiveWindow.SelectedSheets.Delete

WBPath = Application.ActiveWorkbook.Path
OrgFile = Application.ActiveWorkbook.FullName

        Range("B1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Columns("A:M").Select
    Selection.Replace What:="" & Chr(10) & "", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Selection.Columns.AutoFit
    Selection.Rows.AutoFit

    Columns("J:J").Select
        With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

'    Columns("J:J").Select
'        Columns("J:J").ColumnWidth = 100
'            Selection.Rows.AutoFit

    Columns("G:G").EntireColumn.AutoFit
        Range("G2").Select

' Remove/Replace Invalid File Name Characters
 WkbName = Range("H2")
    MyArray = Array("<", ">", "|", "/", "*", "\", ".", "?", """")
    For X = LBound(MyArray) To UBound(MyArray)
        WkbName = Replace(WkbName, MyArray(X), "_", 1)
            Next X
                'MsgBox WkbName     'dispaly file name with illegal characters removed

    ActiveWorkbook.SaveAs Filename:= _
       WBPath & "\BOM_" & Range("G2") & "_" & WkbName & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    If Len(Dir$(OrgFile)) > 0 Then
        Kill OrgFile
            End If

  Application.DisplayAlerts = True

Application.ScreenUpdating = True

 ' MsgBox OrgFile & " has been deleted and saved as " & "BOM_" & Range("G2") & "_" & Range("H2") & ".xlsx"

End Sub
`

please excuse my notes and random crap in the code. I always clean it up before I give it to others

Swacked

Maybe there are illegal characters in G2? It would be a good idea if you posted at actual error message and some sample data – Stripper

before the save line, do a msgbox line to see if the results are correct.msgbox WBPath & "\BOM_" & Range("G2") & "_" & WkbName & ".xlsx" if it is incorect, you will know where to edit your string. – Hayott

How about 'WkbName = Application.WorksheetFunction.Clean(WkbName)' – Touched

Answer 1

Because there could be more illegal characters in the filename. Your approach is right but it's not comprehensive list of illegal characters to remove or replace from the filename before saving it. For eg. these characters are missing from the array in your code -> : & . However it is advised to keep filename rid of other allowed special characters too.

Below, I am providing the function which returns a safe string that can be used to produce filename before saving.

Function ReplaceIllegalCharacters(strIn As String, strChar As String) As String
    Dim strSpecialChars As String
    Dim i As Long
    strSpecialChars = "~""#%&*:<>?{|}/\[]" & Chr(10) & Chr(13)

    For i = 1 To Len(strSpecialChars)
        strIn = Replace(strIn , Mid$(strSpecialChars, i, 1), strChar)
    Next

    ReplaceIllegalCharacters = strIn 
End Function

Specifically, in your code, replace the ActiveWorkbook.SaveAs line with this line:

ActiveWorkbook.SaveAs Filename:= _
   WBPath & "\BOM_" & Range("G2").Value2 & "_" & ReplaceIllegalCharacters(Range("H2").Value2, "_") & ".xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Samples

strSpecialChars(i) doesn't work in VBA. Use Mid$(strSpecialChars, i, 1) instead – Stripper

Thanks Chris - that's what happen when you are working on multiple languages. Sometime I code in C# too. Correcting the code. Thanks a heap for pointing out. – Samples

edited post to include original error message and the one after using your function and code edit @Samples – Swacked

Edited my post again - need to add the provision to include square brackets too! – Samples

odd. I had to restart excel 2 times but its working now. Thanks for your help – Swacked

also any chance there is an easy method to check for sheets2 & 3 before deleting them? ' delete extra sheets Sheets(Array("Sheet2", "Sheet3")).Select ActiveWindow.SelectedSheets.Delete – Swacked

Visit this link: #18594723 – Samples