在保存Excel VBA工作簿时删除非法字符

Question

此代码基本上重新格式化一个xls文件并将其保存为xlsx文件。但是它使用G2和H2来获取新格式文件的文件名。这意味着某些字符不能出现在文件名中。我添加了一段代码来替换这些字符（

）

' 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是调试器经常带我去的地方。

我收到了一个错误消息，即使在 h2 中只有普通文本，也总是会出现非法字符，我错过了什么吗？

完整代码如下：

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
`

在我分享给其他人之前，请原谅代码中可能存在的注释和随意的内容。 我通常会在分享前对其进行清理。

- Alberto Brown

---

G2 中可能存在非法字符？如果您发布实际的错误消息和一些示例数据，那将是一个好主意。 - chris neilsen

在保存代码之前，加入一个msgbox代码行来检查结果是否正确。msgbox WBPath & "\BOM_" & Range("G2") & "_" & WkbName & ".xlsx" 如果结果不正确，你就会知道在哪里修改字符串了。 - Davesexcel

'WkbName = Application.WorksheetFunction.Clean(WkbName)' 这个怎么样？ - Keith Swerling

Answer 1

由于文件名中可能存在更多非法字符，您的方法是正确的，但它不包含从文件名中删除或替换的所有非法字符的综合列表。例如，在您的代码中，这些字符在数组中缺失 -> : &。然而，建议将文件名除其他允许的特殊字符外保持清除。

下面，我提供了一个函数，该函数返回一个安全字符串，可用于在保存之前生成文件名。

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

具体来说，在您的代码中，将ActiveWorkbook.SaveAs行替换为以下行：

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

- jainashish

---

在VBA中，strSpecialChars(i)无法正常工作。请改用Mid$(strSpecialChars, i, 1)。 - chris neilsen

编辑帖子以包括原始错误消息和使用您的函数和代码编辑后的消息 @jainashish - Alberto Brown

我又编辑了我的帖子 - 需要添加包括方括号的规定！ - jainashish

奇怪，我不得不重新启动Excel两次，但现在它可以工作了。感谢您的帮助。 - Alberto Brown

访问此链接：https://stackoverflow.com/questions/18594223/how-to-delete-a-blank-sheet-in-a-workbook-using-vba - jainashish