title: Remove illegal characters while saving workbook Excel VBA
body: <p>this code basically reformats an xls file and saves it as an xlsx. however it uses <code>G2</code> &amp; <code>H2</code> 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 ( </p>
  
  <pre><code>' Remove/Replace Invalid File Name Characters
   WkbName = Range("H2")
      MyArray = Array("&lt;", "&gt;", "|", "/", "*", "\", ".", "?", """")
      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 &amp; "\BOM_" &amp; Range("G2") &amp; "_" &amp; WkbName &amp; ".xlsx" _
          , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 
  </code></pre>
  
  <p>)
  activeworkbook.saves as is where the debugger always takes me</p>
  
  <p>I'm getting an error message saying there's always an illegal character even if its just normal text in <code>h2</code>, am I missing something?</p>
  
  <p>full code below</p>
  
  <pre><code>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:="" &amp; Chr(10) &amp; "", 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("&lt;", "&gt;", "|", "/", "*", "\", ".", "?", """")
      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 &amp; "\BOM_" &amp; Range("G2") &amp; "_" &amp; WkbName &amp; ".xlsx" _
          , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
  
      If Len(Dir$(OrgFile)) &gt; 0 Then
          Kill OrgFile
              End If
  
    Application.DisplayAlerts = True
  
  Application.ScreenUpdating = True
  
   ' MsgBox OrgFile &amp; " has been deleted and saved as " &amp; "BOM_" &amp; Range("G2") &amp; "_" &amp; Range("H2") &amp; ".xlsx"
  
  End Sub
  `
  </code></pre>
  
  <p>please excuse my notes and random crap in the code. I always clean it up before I give it to others</p>
  
  <p><a href="https://i.stack.imgur.com/EMvJ2.png" rel="noreferrer"><img src="https://i.stack.imgur.com/EMvJ2.png" alt="enter image description here"></a></p>