Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Auto Save Macro

  1. #1
    Join Date
    3rd February 2008
    Posts
    3

    Auto Save Macro

    i want to have a macro which will autosave the worksheet/book automatically every minute so the user doesnt have to keep clicking file and save.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Write An Autosave Macro

    Bad idea as you'll sooner or later save something that shouldn't be. E.g Worksheet deletion.

  3. #3
    Join Date
    3rd February 2008
    Posts
    3

    Re: Auto Save Macro

    Hi Dave i understand what you mean re: saving something didnt want to but worksheets are protected so should be ok can the following be done though? " i want to have a macro which will autosave the worksheet/ book automatically every minute so the user doesnt have to keep clicking file and save".

    Cheers Graeme

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Auto Save Macro

    Ok, but don't say you were not warned and Sheet protection wont stop sheet deletion. To stop sheet deletion protect the Workbook and ensure you check Structure.

    Right click on the Excel icon, top left next to File, choose View Code and in here paste;
    VB:
    Private Sub Workbook_BeforeClose(Cancel As Boolean) 
        Application.OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll", , False 
    End Sub 
     
    Private Sub Workbook_Open() 
        DTime = Time 
        Application.OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll" 
    End Sub 
    
    
    Now go to Insert>Module and paste in this;
    VB:
    Public DTime As Date 
     
    Sub SaveMe_MistakesandAll() 
        DTime = Time 
        Application.OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll" 
        ThisWorkbook.Save 
    End Sub 
    
    
    Save, close and re-open with macros enabled and it should save every minute.
    Last edited by Dave Hawley; February 22nd, 2008 at 15:41.

  5. #5
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,654

    Re: Auto Save Macro

    After running the macros, here some additional information.
    Change
    VB:
    Application .OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll", , False 
    
    
    to
    VB:
    Application.OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll", False 
    
    
    Only one ","

    In the module add the SAVE statement
    VB:
    Sub SaveMe_MistakesandAll() 
        DTime = Time 
        Application.OnTime DTime + TimeValue("00:00:07"), "SaveMe_MistakesandAll" 
        ActiveWorkbook.Save 
    End Sub 
    
    
    There is an additional issue: If you close the file and NOT EXCEL the file is opened again.
    Triumph without peril brings no glory: Just try

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Auto Save Macro

    The correct syntax is: expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule) and it's the that must be set to False upon closing, not the LatestTime.

    Other than missing out ThisWorkbook.Save in the SaveMe_MistakesandAll Procedure, my code is correct.

  7. #7
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,654

    Re: Auto Save Macro

    Completely clear now, perhaps the Copy / Paste made was not good, the 2 "," are needed without anything between.
    The Master is still excellent.
    Triumph without peril brings no glory: Just try

  8. #8
    Join Date
    3rd February 2008
    Posts
    3

    Re: Auto Save Macro

    sorry dave have tried these but continues to say ambiguous argument or words to that effect and if i switch off without saving after 5 mins things still dont save?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,654

    Re: Auto Save Macro

    Mackem,
    Find attached a file with macros as shown by Dave in works with a delay of 15s for testing reason.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Triumph without peril brings no glory: Just try

  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Auto Save Macro

    Quote Originally Posted by mackem
    sorry dave have tried these but continues to say ambiguous argument or words to that effect and if i switch off without saving after 5 mins things still dont save?
    You must have 2 Procedures using the same name.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Auto Save As VBA Macro Every X Minutes
    By 777twist in forum EXCEL HELP
    Replies: 6
    Last Post: June 8th, 2013, 00:00
  2. Print To Pdf - Auto Save As
    By Reafidy in forum EXCEL HELP
    Replies: 8
    Last Post: July 12th, 2007, 14:26
  3. Auto Save
    By eddie57 in forum EXCEL HELP
    Replies: 3
    Last Post: February 24th, 2005, 16:56
  4. Auto Save
    By eddie57 in forum EXCEL HELP
    Replies: 3
    Last Post: February 19th, 2005, 05:35
  5. Replies: 2
    Last Post: December 22nd, 2004, 09:28

Bookmarks

Posting Permissions

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