Saveas with date problem

[cereldine]

Hi i have a procedure that copys data into a workbook from external
locations, i need to save this workbook under todays date to create an
audit trail. I'm having problems doing this as running the code brings
up error 1004 - excel cannot access the file because doesn't exist,
used by someone else.

What is wrong, code is below, thanks

Dim fname As String

fname = "Mainfile-" & Format(Date, "dd/mm/yyyy")
ActiveWorkbook.SaveAs Filename:=fname
MsgBox (fname)

 

[aidan.heritage]

You are using slashes in your file name, which isn't allowed - they
indicate a new directory - so try

"mainfile-" & format(date,"dd-mm-yyyy")

should do it for you!

 

[Bob Phillips]

Can't do / in a filename, and you should start with the date in reverse
order, for sorting

Dim fname As String

fname = Format(Date, "yyyymmdd") & "-Mainfile"
ActiveWorkbook.SaveAs Filename:=fname
MsgBox (fname)


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[broro183]

Hi Cereldine,

I normally receive a different (more informative) warning message whe
I try to save files with a date & it tells me that the reason that th
code will not allow a save is that filenames can not include th
forward slash character (ie "/"). The easiest way I know of to ge
around this is to replace it with a decimal point (ie "."), fo
example:

Dim fname As String
Dim StringDate As String
StringDate = Day(Date) & "." & Month(Date) & "." & Year(Date)
fname = "Mainfile-" & StringDate
MsgBox fname
ActiveWorkbook.SaveAs Filename:=fname
MsgBox (fname)

hth
Rob Brockett
NZ
Always leraning & the best way to learn is to experience..