斜杠文件名之间单词的VBA

Question

我构建了一个基于名称列表保存工作簿的宏。
我收到 saveAs 错误，因为某些新列表项在文件名中包含/是不允许的。

我需要一些帮助来编写可以执行此操作的代码:请注意，球队、名称、联赛和杯赛的列表每行都会发生变化。

  list item: team/ league
  what I want: league 

  list item: team / league / cup                    
  What I want: league-cup

  List item; team / league / cup / score
  what I want: league-cup-score

我让它适用于只有一个/但无法休息的第一个场景。

 InStr(Value, "/") > 0 Then
 filename = Right(Value, (Len(Value) - InStr(Value, "/")))
 Else
 filename = Value
 End If

是我到目前为止得到的。

谢谢

Answer 1

您可以拆分、修剪和加入:

Function MakeFileName(s As String) As String
    Dim v As Variant, w As Variant
    Dim i As Long, n As Long
    v = Split(s, "/")
    n = UBound(v)
    ReDim w(1 To n)
    For i = 1 To n
        w(i) = Trim(v(i))
    Next i
    MakeFileName = Join(w, "-")
End Function

例如:

Sub test()
    Debug.Print MakeFileName("team/ league")
    Debug.Print MakeFileName("team / league / cup")
    Debug.Print MakeFileName("team / league / cup / score")
End Sub

输出:

league
league-cup
league-cup-score