My code shows error msg while saving a file with date and time in the file name

Question

i am using the code below to save a file with date and time in the file name. Unfortunately, it shows error msg. Is there a way to avoid this error?

dim a as variant
dim fname as string
dim newWB as workbooks
a = Now()
fname = CStr(a)
Set newWB = Workbooks.Add
newWB.SaveAs Filename:="C:\Users\0003079\Downloads\QiraTickets" & "(" & fname & ")", FileFormat:=56

Because your `fname` have `:` which are not allowed in File Name. — Mikku, Aug 22 '19 at 05:11
what Mikku said. [Slash](https://stackoverflow.com/questions/10708334/how-can-i-create-files-on-windows-with-embedded-slashes-using-python) ('/') and [colon](https://stackoverflow.com/questions/10386344/how-to-get-a-file-in-windows-with-a-colon-in-the-filename) (':') are not allowed in windows file names — mcalex, Aug 22 '19 at 05:13

score 0 · Answer 1 · answered Aug 22 '19 at 05:14

Problems:

Use of : in filename isn't allowed
Declaration as Workbooks, should be workbook

Try:

Dim a As Variant
Dim fname As String
Dim newWB As Workbook

a = Now()

fname = Replace(CStr(a), ":", "'")

Set newWB = Workbooks.Add

newWB.SaveAs Filename:="C:\Users\0003079\Downloads\QiraTickets" & "(" & fname & ")", FileFormat:=56

Yellow highlighted are not allowed

score 0 · Answer 2 · answered Aug 22 '19 at 06:48

From Mikku and McAlex: Slash ('/') and colon (':') are not allowed in windows file names.

However, you can format your date output to something useful before you assign it to the filename

dim a as Date ' <-- always strongly type when you can
dim fname as string
dim newWB as workbook  ' Workbook, not Workbooks
a = Now()  ' Assuming that you want to use a later
fname = Format(a, "yymmdd hhnnss") ' <-- Format will output a string
Set newWB = Workbooks.Add
newWB.SaveAs Filename:="C:\Users\0003079\Downloads\QiraTickets" & "(" & fname & ")", FileFormat:=56

Of course, if a is only used to form the file name, we can tidy this up.

Dim fname As String
Dim newWB As Workbook
fname = "C:\Users\0003079\Downloads\QiraTickets(" & Format(Now(), "yymmdd hhnnss") & ")" 
Set newWB = Workbooks.Add
newWB.SaveAs Filename:= fname, FileFormat:=56

My code shows error msg while saving a file with date and time in the file name

2 Answers2