Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving Excel Files (2000)

    How can I have a macro save the current file with the file name ("Save As") as the current date ?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Excel Files (2000)

    use :
    <pre>Sub SaveAsDate()

    ActiveWorkbook.SaveAs Filename:="D:data2000excel" & Format(Date, "mmddyy")

    End Sub</pre>

    Change the format like you want but don't use / (and in my opinion not - and .) as separators.
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Saving Excel Files (2000)

    You could use the VBA Format function to convert the value of NOW() to a string representing the date, e.g. fileName = Format(Now(), "MM-DD-YY"). You can format the date as required and include the time if needed to avoid overwriting :<pre> Sub SaveAsDate()
    Dim strFileName as String
    strFileName = <font color=red>"C:My Documents"</font color=red> & Format(Now(), "YYYY-MM-DD hhmmss")
    ActiveWorkbook.SaveAs strFileName
    End Sub</pre>

    You might need to change the path reference (in red) to something more appropriate to your requirements

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Excel Files (2000)

    I was just informed that I hav not responded to the solutions to my question.
    Pardon me...no REALLY, PARDON ME.
    Thanks. I've used a combination of both responses successfully.
    Thanks to both Francois & Andrew.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving Excel Files (2000)

    the following variation on the responses by Andrew and Francois will allow you to retain the original Workbook name as your working copy.
    <pre>Option Explicit


    Public Sub SaveCopy()
    'In the following line replace "C:Safeplace" with your required path
    ActiveWorkbook.SaveCopyAs "C:Safeplace" & ActiveWorkbook.Name _
    & Format(Date, "mmddyy")
    End Sub
    </pre>

    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •