AutoSave in VBA

[patrick_m84]

Hi Guys

Wondering if you could help....

We have just had a power surge in the office and as such the majority of data in excel that people were using was lost.

The initial solution to stop this happening again was to use the AutoSave add in. However, because several copies have not been installed properly, this add-in is in an unavailable location on a server.

I reckon it must be fairly straightforward to write a quick VBA jobby that will save every 20 minutes.... semantically i guess it would be like this:

initial time = now()

when now() = initial time + 20
save active worksheet
initial time = now()
end

would that kinda thing work? and also how could i get it so that the macro would be available to all workbooks. i know it has something to do with the public excel folder or something, but i am a little unsure

anyhelp received would be very much appreciated.

 

[DRJ]

Hi - Welcome to the board

Sub SaveIt()

Thisworkbook.Save

Application.OnTime Now + TimeSerial(0, 20, 0), "SaveIt"

End Sub


Then in the workbook open just run saveit and it will keep looping every 20 minutes.

 

[patrick_m84]

Thanks Jacob

Quick question though, how would I get this code running behind every workbook open?

I.E when the user opens excel, i want this code to run whatever file they open within it....

 

[DRJ]

That would take a class module to group all the open events. I'm don't use class modules that much so I am still learning them myself. Search the board for class module, there are a few topics which my help.

You could also make your own adding, but you would have to change the code from thisworkbook to do a loop for all the open workbooks.

Post back if you need help with any of these.

 

[Jan Erik]

Hi - Welcome to the board

Sub SaveIt()

Thisworkbook.Save

Application.OnTime Now + TimeSerial(0, 20, 0), "SaveIt"

End Sub


Then in the workbook open just run saveit and it will keep looping every 20 minutes.

Jacob,

I thank you for this tip, It's useful for me also

But I get a problem; After closing the workbook - it reopens automatically.
Do I need any termination code in Before_Close or something?

Jan Erik
Norway

 

[kwilliam10]

I'd be careful with this. There are MANY times I am messing around with a worksheet (running what-ifs, for example). I'd be plenty pissed if something save my workbook directly (that I didn't want save), and not to some dummy file.



Keith W.

 

[Bawjawz]

cheers worked great for me

 

[devonknows]

I have a very similar routine to this, but i am also experiencing similar problems as desribed above. The document, due to the extensive amounts of VBA in the background does not autosave/autorecover correctly so i entered a new function to autosave the document.

Every 10 Minutes, the document will re-open agian (providing i havnt fully closed down excel) and save the document and leave it open.

Sub Workbook_Close()

Call VBAStopAutoSave

Sub VBAAutoSave

Public ThisTime as Double
Sub VBAStopAutoSave()
    On Error GoTo ErrH:
    Application.OnTime ThisTime, Procedure:="SaveDocument", Schedule:=False

Exit Sub

Sub SaveDocument()

Sub SaveDocument()
    On Error GoTo ErrH
    ThisWorkbook.Save
    Call ResetVBASaveTimer

Exit Sub

Sub ResetVBASaveTimer

Sub ResetVBASaveTimer()
On Error GoTo ErrH
    ThisTime = Now + TimeValue("00:10:00")
    Application.OnTime ThisTime, Procedure:="SaveDocument"

Exit Sub

Can Any please give some advice on how to ensure that when the document is closed that the timer that is created for that document is stopped and doesnt continue to open the documents affter they have been closed?

Regards,
Devon

 

[Mon Vichai]

1. Save file without alert information when you close your excel file
2. coding in VBA excel:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save

End Sub

 

[excel2030]

thanks