Saveas with date problem

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)

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!

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)

"cereldine" <[email protected]> wrote
in message news:[email protected]...
>
> 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)
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=539204
>

Hi Cereldine,

I normally receive a different (more informative) warning message when I try to save files with a date & it tells me that the reason that the code will not allow a save is that filenames can not include the forward slash character (ie "/"). The easiest way I know of to get around this is to replace it with a decimal point (ie "."), for 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...