Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2005
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Have Excel Save Workbook every night (Excel 2003)

    I have an Excel spreadshhet. I leave it running 24 /7. It is automatically updated each night from another system. I need it to automatically save the spreadshhet each night around 11:30 PM. I want to save it to my desktop and the name to change each night. Say maybe last night save it as 3/20/08. The following day save it as 3/21/08 and so on. Can the system do this automatically for me.

    Thanks in Advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Have Excel Save Workbook every night (Excel 2003)

    You can add this to the the thisworkbook module so when the file is opened it will tell excel to run the procedurfed "Saveme" at 11:30 PM
    <pre>Private Sub Workbook_Open()
    Application.OnTime TimeValue("23:30:00"), "SaveMe"
    End Sub</pre>


    Then in a normal module add the procedure:
    <pre>Sub SaveMe()
    ThisWorkbook.SaveAs Format(Date, "m-dd-yy")
    Application.OnTime TimeValue("23:30:00"), "SaveMe"
    End Sub</pre>


    This will save the file with a name like 3-20-08.xls [(you can not use the slashes (/) since they are not allowed in filenames] and then tell XL to run the SaveMe procedure again at 11:30 PM the next time it comes around...

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Have Excel Save Workbook every night (Excel 2003)

    <P ID="edit" class=small>(Edited by wdwells on 20-Mar-08 07:17. "line" was originally misspelled as "tile".)</P>Further to Steve's solution; since the system which is updating this file may be confused by changing filenames you may want to change the line which reads:
    ThisWorkbook.SaveAs Format(Date, "m-dd-yy")

    To:
    ThisWorkbook.SaveCopyAs Format(Date, "m-dd-yy") & ".xls"
    Regards
    Don

Posting Permissions

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