Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically Save as Date

    Anyone know of a way to automatically save as the current date? (on userform button click?)

    Thanks!

    o97sr2
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Automatically Save as Date

    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    I know I can retain the date in a cell, i'm looking for a method of saving the file as the date, appended with the user's initials. any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    Try this:

    ThisWorkbook.SaveAs "c:" & Format(Date, "m-d-yy") & "XX"

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    do you think "xx" could be a variable?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    DRK,

    I think "XX" might be your user's initials- derived from the setting in Tools, Options, Settings, Username maybe? That's available from Application.UserName
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    We all return "Registered User" from Application.Username...
    How can I make a dialog box which would allow the user to enter his/her initials and retain that to a variable to replace "xx" in this instance?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    Try:

    Dim strInitials As String
    strInitials = InputBox("Enter Initials:")

    You may want to save those settings, so that the user doesn't have to enter every time.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    This code should act just like clicking file, save as, correct? For some reason, it is not saving the file as strinitial & Format(Date, "mm-dd-yy"). Any Ideas?

    My exact code for this portion is below.

    Thanks again!

    ***CODE START***
    sub btnsave_click()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "c:windowstemp" & strInitials & Format(Date, "m-d-yy") & ".xls"
    Application.DisplayAlerts = True
    end sub
    ****CODE END****
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    Where have you set up strInitials?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    Public.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    Have you checked that there are values in it before it tries to save the worksheet?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically Save as Date

    Try a after Temp
    David Grugeon
    Brisbane Australia

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    I see several things here:

    1- You need a "" after the "temp" directory name. What you have is putting "temp" in front of the user's initials and storing the file in the C:Windows directory.

    2- If you want these files to sort in the order created in Windows Explorer, then you should change the format of the date used in the name to "yyyy-mm-dd".

    3- It would be a lot easier to see what was going on, especially when you step through or put in a breakpoint if you change the code like this:

    <pre>sub btnsave_click()
    Dim strFileName
    strFileName="c:windowstemp" & strInitials & Format(Date, "yyyy-mm-dd") & ".xls"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs strFileName
    Application.DisplayAlerts = True
    end sub
    </pre>


    Now, you can hover the cursor over strFileName as see what you are doing.
    Legare Coleman

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Save as Date

    Wow!

    But, anyone who uses this code ver-batum should know, there are parenthesis around the save-as String.

    Thanks Millions Legare!!!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

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