Macros and VBA

Occasional Visitor

How can I substitute "-" for "/" in the dates so I can use the dates in the file name?

NewFile = "Financial Report " & Sdate & " to " & EDate & "_" & Unique & ".xlsx"

Sdate and Edate are from a column formated as DATE so always have the slash when used in a formula

and the slash is not valid in a file name.

Financial Report 11/15/2020 to 5/4/2022_2022-05-27 2007.xlsx

I use the following code to employ the date&time to make the file name unique even if you create many files in a day. 

ActiveCell.FormulaR1C1 = "=TEXT(NOW(),""YYYY-mm-dd_hhmm"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False
Unique = Selection

 

1 Reply

@Begin2022 

NewFile = "Financial Report " & Format(Sdate, "yyyy_mm_dd") & " to " & Format(EDate, "yyyy_mm_dd") & "_" & Unique & ".xlsx"