Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Saving files automatically while u work

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.

    I had the unpleasent experience this morning of losing 3 hours worth of work on excel. Yes, silly me not saving the work as I progressed and having to rewrite a tricky macro all over again.

    However, I was wondering if anyone had some sort of code that would mean the file I was working on would be saved periodically as something else, temporarily. I do not always want to save the file I am working on with autosave, since as I experiment a lot with things I only want to save the file at certain stages, when I have got things working correctly.

    But, having a backup of the most recent version of the file stored somewhere else to be retrieved in the event of a crash would certainly be very useful indeed.

    Thanks in advance,

    RET79

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Goto Tools|Add Ins, there should be one calle "Auto Save Add In" or something.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark,

    I have the autosave thingy, but am not too keen on it personally - the reason being that I mess about a lot, and alter some things and don't want all that crap being saved. Also when u make it prompt u it drives me nuts as it keeps on interrupting.

    Does anyone know if you can put some code somewhere that would mean it would save itelf as a temp file continuously somewhere else without having to prompt me and save all my messing about. If this was possible then I could just save the workbook when I want at meaningful stages.

    Thanks.

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 10:48, Mark O'Brien wrote:
    Goto Tools|Add Ins, there should be one calle "Auto Save Add In" or something.
    Then...

    When you open your book, do a Save As and save it under a different name, or in a different folder.

    When you've got what you want and it's working, do another Save As, but this time return it to its original name.

    How's that?


  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code go's in "ThisWorkBook" it will auto save after three sheet calculations or re-calculation, automatically. Or, you can use the optional,"Time to Save!" message. Hope this helps. JSW

    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim myTest As Integer
    Static x As Integer

    x = x + 1
    myTest = x
    If myTest >= 3 Then
    ActiveWorkbook.Save
    'MsgBox "Time to Save"
    x = 0
    Else
    GoTo myEnd
    End If
    myEnd:

    End Sub

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Joe,

    I certainly think that you are on the right track to what I want.

    Yes, I want something with code in the workbook.

    But, basically what I really need is this.

    For instance, say I am working on a file called Master.xls and then I decide to mess about. I need for my work to be saved automatically every 5 minutes or so UNDER ANOTHER NAME while I can keep on working and saving what I do manually under Master.xls

    I have had 2/3 instances of late where very important work was lost, and everytime I have been thinking that the answer would be whatever I am doing to be automatically saved under some other name in some other folder without it affecting what I am doing.

    Would it be possible to use the ontime function for this? And instead of .Save could we have SaveAs "somethingelse.xls" but then carry on working in the master.xls workbook?

    RET79

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub RunAgain()
    mRunTime = Now + TimeValue("2:00:00") 'adjust to suit time needed
    Application.OnTime mRunTime, "RunAgain"
    Application.StatusBar = "Saving Latest Data ...."
    Application.Wait Now() + TimeValue("0:0:02")
    ActiveWorkbook.Save

    Application.StatusBar = False
    End Sub


    'stop macro from running automatically
    Sub StopIt()
    On Error Resume Next
    Application.OnTime mRunTime, "RunAgain", _
    schedule:=False
    End Sub

    that would help
    put the stopit file in the workbook before close


    [ This Message was edited by: brettvba on 2002-04-18 18:42 ]

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    Centerville, Indiana
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Go to this website http://www.bmsltd.co.uk/MVP/MVPPage.asp and look for autosafe.zip. I haven't used it yet but I think it's what your looking for. Autosave is really distracting.

  9. #9
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    As before this code go's in "ThisWorkbook."

    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim myTest As Integer
    Static x As Integer

    x = x + 1
    myTest = x
    If myTest >= 3 Then
    ThisWorkbook.SaveAs Filename:="3" & ThisWorkbook.Name
    x = 0
    Else
    GoTo myEnd
    End If
    myEnd:

    End Sub

    After 3 sheet calculations, your file is saved with a "3" infront of the current file name. So "File" becomes "3File" which after 3 calculations then becomes "33File" and so on. The current file is the file with the most "3's" infront of the filename.

    My first code is better as this code will fill up your hard drive with copies of your work!

    Between your "OnTime" code and my two calc codes you should be able to come up with the combination you want. JSW

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks for all your help guys, but this time i have to say that the add in suggested by first hob was perfect for my needs just now!
    I recommend u go download it, it rocks.

    RET79

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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