Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: How to Save a Date into the Filename of a spreadhseet?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello again,

    I am looking for macro code that will automatically insert a user-specified date (in yyyymmdd format) into an XL filename.

    For instance, a typical filename would be "Reg 05_Weekly_20020209.xls" where the "Reg05_Weekly_" portion would remain constant, but the date "20020209" would have to reflect the date specified by the person running the macro. I would probably want to insert an "input Box" where the user would be prompted to enter this past Saturday's date in "yyyymmdd" format.

    Any suggestions on how to pull this off?

    Any assistance here would be greatly appreciated.

    Thanks,

    Rob


  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:

    ThisWorkbook.SaveAs Filename:=WorksheetFunction.Substitute(ThisWorkbook.Name, ".xls", "_") & WorksheetFunction.Substitute(Date, "/", "")

    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-03-18 13:05 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-18 12:12, Al Chara wrote:
    Try the following code:

    ThisWorkbook.SaveAs Filename:=WorksheetFunction.Substitute(Date, "/", "")

    Hope this helps.
    Kind regards, Al.
    Cool! Thats definitely what I had in mind.. only thing is, how do I append this date to the end of a constant("Reg_01_") so that the renamed file is now named "Reg_01_20020316.xls", etc.?

    Your help is greatly appreciated

    Rob

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the updated code in my earlier post.

    Hope this helps.
    Kind regards, Al.

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following procedure combines your constant with the date of last saturday based on todays date:
    Code:
    Sub LastSat()
    Dim nth As Variant, last As Integer
    last = WeekDay(Date)
    nth = Format(Date - last, "yyyymmdd")
    ThisWorkbook.SaveAs Filename:="Reg_01_" & nth
    End Sub
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: nateo on 2002-03-18 14:56 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-18 13:14, NateO wrote:
    The following procedure combines your constant with the date of last saturday based on todays date:
    Code:
    Sub LastSat()
    Dim nth As Variant
    nth = DateSerial(year(Date), Month(Date), (8 - Weekday(DateSerial(year(Date), Month(Date), 1), _
         (7 + 1) Mod :cool:) + ((3 - 1) * 7))
    nth = Format(nth, "yyyymmdd")
    ThisWorkbook.SaveAs Filename:="Reg_01_" & nth
    End Sub
    Wow, its getting there! That worked too. I understand your logic, but still having a little trouble following...

    Really what I'd like to do is just have the macro reference a cell in this worksheet (to be renamed) which already has the date specified (the user specifies the date already in an earlier macro step). In this case its cell "C3"..and contains the "Past saturday's" date in "Month Day, Year" format (Ie, March 16, 2002)..

    I would like to just have the macro reference that cell, change the date formatting so that is appears as "20020316" and then append that date to the end of "Reg1_" , as before.

    Any suggestions here?

    Thanks for your patience (all of you).

    Rob

  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:

    ThisWorkbook.SaveAs Filename:=WorksheetFunction.Substitute(ThisWorkbook.Name, ".xls", "_") & Format(Range("c3").Value, "yyyymmdd")

    This code takes the the value in cell C3 (assumes it is in date format mm/dd/yyyy) and changes it to yyyymmdd and then adds it to the existing file name. I assumed that the file name was already Reg 05_Weekly.xls. Is this true, or do you want the Reg 05_Weekly_ generated also?

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try This:

    Sub LastSat2()
    Dim nth As Variant
    nth = [c3].Value
    nth = Format(nth, "yyyymmdd")
    ThisWorkbook.SaveAs Filename:="Reg_01_" & nth
    End Sub

    Change "Reg_01_" to whatever you want your constant to be.

    HTH.

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmm..having a little trouble.. it seems to ignore the date and only places the "Reg_01_" part in the filename. Well I must leave for the day but I will pick right up here tomorrow morning.

    Thanks again for your help

    Rob

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Howdy. I changed the code in my earlier post as the value 3 was a plugged value for the 3rd Sat. in the month, wouldn't have worked next week. But, I revised the post, it should work any week. You may want to reconsider having the user enter last saturday, might just be easier to have VBA do this for you like above. Less room for error.

    However, you're the boss. If you want cell c3, make sure c3 has a date in it, like below:

    Code:
    Sub LastSat3()
    Dim nth As Variant
    nth = [c3].Value
    If IsDate(nth) Then
    nth = Format(nth, "yyyymmdd")
    Else: MsgBox ("Invalid Date" & Chr(13) & Chr(13) & "Check cell C3")
    Exit Sub
    End If
    ThisWorkbook.SaveAs Filename:="Reg_01_" & nth
    End Sub
    Hope this helps.
    ________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-18 17:10 ]

Some videos you may like

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
  •