保存工作簿Excel VBA时删除非法字符 [英] Remove illegal characters while saving workbook Excel VBA

问题描述

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

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是调试器总是带我的地方

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

我收到一条错误消息，说总是有非法字符，即使它只是h2中的普通文本，我是否也遗漏了一些东西?

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?

下面的完整代码

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

推荐答案

因为文件名中可能包含更多非法字符.您的方法是正确的，但是在保存文件名之前，它不是从文件名中删除或替换的非法字符的完整列表.例如.这些字符从您的代码数组->中丢失:& .但是，建议也将文件名除去其他允许的特殊字符.

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

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

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

这篇关于保存工作簿Excel VBA时删除非法字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！