Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    add save date and time to a save name automaticall (excel 2003)

    Hello,
    I am wondering if anyone knows the command or way to add the date to the little code snip i have here: (CFN is the final save name I would like to add the date to before the .cfg)

    osaveto = Iwsh.Range("R2").Value
    oProgramNumber = oInput.Range("ProgramNumber").Value
    oLocation = oInput.Range("WellLocation").Value
    oLocation = Replace(oWellLocation, "/", " ")
    oCompanyName = oInput.Range("Companyname").Value
    CFN = osaveto & oProgramNumber & " " & oCompanyName & " " & oLocation & ".cfg"

    ActiveWorkbook.SaveAs Filename:=CFN, FileFormat:=xlTextWindows
    ActiveWorkbook.Close SaveChanges:=False

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

    Re: add save date and time to a save name automaticall (excel 2003)

    Try

    CFN = osaveto & oProgramNumber & " " & oCompanyName & " " & oLocation & Format(Date, " mmddyyyy") & ".cfg"

    Note: You can modify the format if you like, but keep in mind that slashes / are not allowed in a file name.

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

    Re: add save date and time to a save name automaticall (excel 2003)

    I would make the format "yyyy-mm-dd" so that the files would sort into the correct order.
    Legare Coleman

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

    Re: add save date and time to a save name automaticall (excel 2003)

    Good idea, but then, he may want to put the date at the beginning of the filename too.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add save date and time to a save name automaticall (excel 2003)

    Thank you I have made the change, The date come through in the save name but somehow the osaveto & oProgramNumber & " " & oCompanyName & " " & oWellLocation varibles are cancelled out and the file is just named mm-dd-yyyy

  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add save date and time to a save name automaticall (excel 2003)

    my apologies I was running my code from a stop point it works fine
    thanks

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

    Re: add save date and time to a save name automaticall (excel 2003)

    That may or may not be what he would want, depending on how he wants things grouped on the other elements of the name. I would tend to put it at the beginning.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add save date and time to a save name automaticall (excel 2003)

    Thank you both for your help,

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add save date and time to a save name automaticall (excel 2003)

    I have a question:


    Cocuments and SettingssallenDesktopCanada Cement

    if you wanted to set up a save button in a workbook which is universal to anyone. The save button macro would save the file to the same place on every computer under Cocuments and Settingsuser1My DocumentsNewFolder

    in this situation how would you reconize the user1 after documentandsettings. I wonder this because this would change for different users. is there a way to make this universal or able to change puting the current user in this spot?

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

    Re: add save date and time to a save name automaticall (excel 2003)

    The current user's desktop folder is

    CreateObject("WScript.Shell").SpecialFolders("Desk top")

    and his/her My Documents folder is

    CreateObject("WScript.Shell").SpecialFolders("MyDo cuments")

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add save date and time to a save name automaticall (excel 2003)

    = "Cocuments and Settings" & CreateObject("WScript.Shell").SpecialFolders("Desk top") & "Canada Cement"
    so this is what i would have to do?>

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

    Re: add save date and time to a save name automaticall (excel 2003)

    Yep.

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add save date and time to a save name automaticall (excel 2003)

    one more curveball
    if I add
    Mkdir ("Cocuments and Settings" & CreateObject("WScript.Shell").SpecialFolders("MyDo cuments") & "testtest2")
    into a save macro, i get the the following error "run time error "76":
    Path not found
    is there somoe sort of addition needed?

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add save date and time to a save name automaticall (excel 2003)

    I found the error in My Documents

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add save date and time to a save name automaticall (excel 2003)

    Mkdir ("Cocuments and Settings" & CreateObject("WScript.Shell").SpecialFolders("MyDo cuments") & "testtest2")

    CreateObject("WScript.Shell").SpecialFolders("MyDo cuments") seem to not do anything

    i set up a test varible to check it
    Mkdir ("Cocuments and Settings" & CreateObject("WScript.Shell").SpecialFolders("MyDo cuments") & "testtest2") returns Cocuments and Settingstesttest2

Page 1 of 2 12 LastLast

Posting Permissions

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