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

Maybe there are illegal characters in `G2`? It would be a good idea if you posted at actual error message and some sample data — chris neilsen, Jun 13 '18 at 21:19
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. — Davesexcel, Jun 13 '18 at 21:23
How about 'WkbName = Application.WorksheetFunction.Clean(WkbName)' — Keith Swerling, May 03 '21 at 19:38

jainashish · Accepted Answer · 2018-06-19T12:50:09.193

27

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

edited Jun 19 '18 at 12:50

answered Jun 14 '18 at 01:05

jainashish

4,702
5
37
48

1

`strSpecialChars(i)` doesn't work in VBA. Use `Mid$(strSpecialChars, i, 1)` instead – chris neilsen Jun 14 '18 at 01:17
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. – jainashish Jun 14 '18 at 01:26
1

edited post to include original error message and the one after using your function and code edit @jainashish – Alberto Brown Jun 19 '18 at 12:33
1

Edited my post again - need to add the provision to include square brackets too! – jainashish Jun 19 '18 at 12:49
odd. I had to restart excel 2 times but its working now. Thanks for your help – Alberto Brown Jun 19 '18 at 13:02
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` – Alberto Brown Jun 19 '18 at 13:08
1

Visit this link: https://stackoverflow.com/questions/18594223/how-to-delete-a-blank-sheet-in-a-workbook-using-vba – jainashish Jun 19 '18 at 23:48

Remove illegal characters while saving workbook Excel VBA

1 Answers1

Linked

Related