![如何在 VBA 中保存之前替换文件名中的禁用字符?](https://linux22.com/image/1447091/%E5%A6%82%E4%BD%95%E5%9C%A8%20VBA%20%E4%B8%AD%E4%BF%9D%E5%AD%98%E4%B9%8B%E5%89%8D%E6%9B%BF%E6%8D%A2%E6%96%87%E4%BB%B6%E5%90%8D%E4%B8%AD%E7%9A%84%E7%A6%81%E7%94%A8%E5%AD%97%E7%AC%A6%EF%BC%9F.png)
对于比我更了解 VBA 的人来说,这将非常简单。我使用了论坛上用户 Nixda 提供的代码原件来根据列值将 Excel 工作表拆分为多个 CSV 文件 (非常感谢 Nixda!)。
我唯一的问题是,其中一些列值包含特殊字符(~ " # % & * : < > ? { | } / ),因此在保存时会出现错误,因为 CSV 文件名由列值决定,而这些是文件名的禁止字符。
我可以添加一些额外的代码来用下划线替换文件名中的禁用字符,但是不是在列值中?
Sub GenerateCSV()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
iCol = 4 '### Define your criteria column
strOutputFolder = "CSV output" '### Define your path of output folder
Set ws = ThisWorkbook.ActiveSheet '### Don't edit below this line
Set rngLast = Columns(iCol).Find("*", Cells(1, iCol), , , xlByColumns, xlPrevious)
ws.Columns(iCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUnique = Range(Cells(2, iCol), rngLast).SpecialCells(xlCellTypeVisible)
If Dir(strOutputFolder, vbDirectory) = vbNullString Then MkDir strOutputFolder
For Each strItem In rngUnique
If strItem <> "" Then
ws.UsedRange.AutoFilter Field:=iCol, Criteria1:=strItem.Value
Workbooks.Add
ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=[A1]
strFilename = strOutputFolder & "\" & strItem
ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False
End If
Next
ws.ShowAllData
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
答案1
为了简单起见,只需在代码之前添加以下行strFilename = strOutputFolder & "\" & strItem
:
strItem= replace(strItem, "~", "_")
strItem= replace(strItem, """", "_")
strItem= replace(strItem, "%", "_")
strItem= replace(strItem, "#", "_")
' and so long ...
strFilename = strOutputFolder & "\" & strItem
[更新] 好吧,正如@Dave提到的(他是对的),这样可以做得更好:
Function ReplaceSpecialChars(strIn As String, strChar As String) As String
Dim strSpecialChars As String
Dim i As Long
strSpecialChars = "~""#%&*:<>?{|}/"
For i = 1 To Len(strSpecialChars)
strIn = Replace(strIn , strSpecialChars(i), strChar)
Next
ReplaceSpecialChars = strIn
End Function
...然后像这样调用它:
strFilename = strOutputFolder & "\" & ReplaceSpecialChars(strItem, "_")