Replace illegal characters when naming file with cell value

[2Took]

Cell A2 contains the following:

ABC123 List : 8/5/2022 2:53:31 PM

My code below encounters following error:

How to Replace illegal characters when naming file with cell value?

Sub MySaveTabName()
With CreateObject("Scripting.FileSystemObject")
ActiveSheet.Name = "ABC - " & Format(Now(), "MM.dd.yy")
MyName = ActiveSheet.Range("A2").Value
End With
Const csPath As String = "C:\Test\"
ActiveWorkbook.SaveAs Filename:=csPath & MyName & ".xlsx"
End Sub

 

[Joe4]

"/" are not legal characters that can be used in file names.
So you will need to either change what is contained in cell A2, or "clean it up" first in your VBA code.

Why not just use the same formula you are using for the sheet name, i.e.

MyName = "ABC - " & Format(Now(), "MM.dd.yy")

 

[2Took]

"/" are not legal characters that can be used in file names.
So you will need to either change what is contained in cell A2, or "clean it up" first in your VBA code.

Why not just use the same formula you are using for the sheet name, i.e.

MyName = "ABC - " & Format(Now(), "MM.dd.yy")

Did cross my mind, but would be interesting to see a VBA solution to replace the illegal characters and in the file name itself, not on the worksheet. Perhaps even if there's a mapping collection of some kind already in Excel that identifies all the illegals and maps them individually to suitable legal counterparts...

 

[Joe4]

but would be interesting to see a VBA solution to replace the illegal characters and in the file name itself, not on the worksheet

You could use the REPLACE function to replace all the backslashes with something else, like dashes, i.e.

    Dim fname As String
    fname = Replace(Range("A2"), "/", "-")
    MsgBox fname

Perhaps even if there's a mapping collection of some kind already in Excel that identifies all the illegals and maps them individually to suitable legal counterparts...

People have done stuff like this before. If you are interested in seeing the code that people have come up, just do some Google searches.
Here is one to get your started, but there are tons more you can find.

VBA function to replace illegal characters in a string - EXCELCISE

Replace illegal (or better to say reserved) characters in a string with a simple VBA function, with the help of regular expression.

www.excelcise.org