Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to add date to a filename (2003)

    I would like to add a date to the end of a filename for a report that is produced each day. The date format should be in YYYY-MM-DD.
    Cell A2 on the second sheet "Games WinLoss" contains the date (and then below that other data) which I paste from another source.

    Using the recorder I can get the file to save and print and then start an email with this (today's) report as an attachment

    So far, the macro looks like this:

    Sub SavePrintGetDateStartEmail()
    '
    ' SavePrintGetDateStartEmail Macro

    ActiveWorkbook.Save
    Range("A10").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1

    Application.Dialogs(xlDialogSendMail).Show

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to add date to a filename (2003)

    You could use the following code:

    Dim strName As String
    Dim intPos As Integer
    strName = ActiveWorkbook.FullName
    intPos = InStr(strName, ".xls")
    If intPos = 0 Then
    MsgBox "Workbook hasn't been saved yet!", vbExclamation
    Exit Sub
    End If
    strName = Left(strName, intPos - 1) & Format(Date, "yyyy-mm-dd") & ".xls"
    ActiveWorkbook.SaveAs strName

    Note: this code doesn't check whether the name (before .xls) already ends in a date. It just tacks the current date after the name (before .xls).

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to add date to a filename (2003)

    Hans,
    I see that this macro adds today's date to the filename. Is there a way that I can get a date from a cell in the worksheet that may not be today's date?

    On Monday's I create reports for Friday, Saturday and then Sunday (I've accidentally overwritten a file or two because I'm doing it manually and didn't change the filename before saving).

    I import data from another source and work that over for my report. But the imported data always has a date in Cell A2 on the second sheet "Games WinLoss".

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to add date to a filename (2003)

    Change the line

    strName = Left(strName, intPos - 1) & Format(Date, "yyyy-mm-dd") & ".xls"

    to

    strName = Left(strName, intPos - 1) & Format(Worksheets("Games WinLoss").Range("A2"), "yyyy-mm-dd") & ".xls"

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to add date to a filename (2003)

    Thanks Hans. Works like a charm. I can try to use this in other workbooks as well.

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to add date to a filename (2003)

    Just a thought...

    Would it not make better sense to place the date before the filename? Sure helps with sorting and later locating the appropriate file.

    I use filenames like the following:
    2008 08 29 - Weekly Report
    2008 08 30 - Weekly Report
    2008 08 30 - Employee Listing
    etc.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

Posting Permissions

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