Announcement

Collapse
No announcement yet.

Auto Save Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Re: Write An Autosave Macro

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

    Comment


    • #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

      Comment


      • #4
        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;
        Code:
        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;
        Code:
        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, 16:41.

        Comment


        • #5
          Re: Auto Save Macro

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

          In the module add the SAVE statement
          Code:
          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

          Comment


          • #6
            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.

            Comment


            • #7
              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

              Comment


              • #8
                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?

                Comment


                • #9
                  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
                  Triumph without peril brings no glory: Just try

                  Comment


                  • #10
                    Re: Auto Save Macro

                    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.

                    Comment

                    Working...
                    X