Append Date
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Append Date

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Does anybody know how to use VBA to save a file with with date appended onto the end? Eg save "file1" as "file1 01-01-02"? If necessary the date can be entered into one of the fields in the spreadsheet.

    [ This Message was edited by: y2k on 2002-03-05 07:30 ]

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ActiveWorkbook.SaveAs Filename:="file1 " & Date & ".xls"

    Will save the file with todays date

    ActiveWorkbook.SaveAs Filename:="file1 " & Range(Date) & ".xls"

    Will save the file with the date stored in the range "Date" on the worksheet



    [ This Message was edited by: Steve Hartman on 2002-03-05 07:38 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If the date is in Cell A1 for example,
    then just get that in to a variable...

    myDate = Range("A1").Value

    And then save your file:

    ActiveWorkbook.SaveAs Filename:="File 1 " & mydate & ".xls"

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Help... it's not working! What am I doing wrong? When I run the macro, I get an error message saying it can't access the file "C:file1 05

  5. #5
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    "C:\file1 05\03"

    Are the double slashes actually in your code or is it just the way it shows up on this board? If you do have double slashes there try it with single slashes.

    [ This Message was edited by: Steve Hartman on 2002-03-05 08:15 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think it's the fact the the & Date & produces a date with a slash as the day/month delimiter and you can't have slashes in your filenames.

    The following worked for me. Write the date to a cell, pick up it's components and then save (substitute in your own path)...

    --
    Range("A1").Formula = Now()
    Range("B1").FormulaR1C1 = "=DAY(RC[-1])"
    Range("C1").FormulaR1C1 = "=MONTH(RC[-2])"
    Range("D1").FormulaR1C1 = "=YEAR(RC[-3])"

    MyDay = Range("B1").Value
    MyMonth = Range("C1").Value
    MyYear = Range("D1").Value

    ChDir ("C:temp")

    ActiveWorkbook.SaveAs Filename:="All_claims " & MyDay & "-" & MyMonth & "-" & MyYear & ".xls"

    --
    This called my file
    "All_claims 5-2-2002.xls"
    It ain't elegant, but it works.

    Rgds
    AJ

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no... the double slashes are the way the board is showing it. I've only gone single slashes, excep at the beginning of the UNC, but that's supposed to have 2 slashes

  8. #8
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    AJ is right, the slash in the filename is the problem.

    A slightly neater way would be:

    ActiveWorkbook.SaveAs Filename:="All_claims " & Month(Date) & Day(Date) & Year(Date) & ".xls"

    to save as todays date

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Ireland
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    It works perfect!! Thank you both very much

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com