excel - 保存文件时忽略斜线

Question

我有一个报告，它选择运行报告的帐号和名称并将其保存在报告位置

一些帐户的名称中有经典的“交易为”，在保存的文件中为 T/A - 微软无法保存

Dim AccCode As Variant, AccName As Variant, MyFileName As Variant, MyfilePath As Variant
Dim ws As Worksheet

MyfilePath = "N:\Solutions\Sales Report"
Set ws = Worksheets("PAGE 1-ACCOUNT&CONTACTS")
AccCode = ws.Cells(5, 2).Value
AccName = ws.Cells(6, 2).Value

MyFileName = MyfilePath & "\" & Trim(AccCode) & "_" & Trim(AccName) & ".xlsx"
'
    ChDir _
    MyfilePath
ActiveWorkbook.SaveAs FileName:= _
    MyFileName _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWorkbook.Save

有谁知道忽略/替换任何 / 的方法以上述方法保存时在文件名中？

Answer 1

以下函数将文件名中的无效字符替换为下划线:

Public Function CreateValidFilename(ByVal name As String) As String
    Const InvalidChars As String = "/:*?""'<>|"
    Dim i As Integer
    For i = 1 To Len(InvalidChars)
        Dim chr As String
        chr = Mid(InvalidChars, i, 1)
        name = Replace(name, chr, "_")
    Next i
    CreateValidFilename = name
End Function

只需添加一个调用，例如

MyFileName = CreateValidFilename(MyFileName)