Saving a file automatically everytime it is closed

[Wombat]

I'd like to be able to save a copy of a document onto my local drive
everytime I change the original on our server. Is there a macro I can use to
save it automatically everytime the document on the server is closed?

 

[Jacob Skaria]

Set the security level to low/medium in (Tools|Macro|Security). From workbook
press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview
search for the workbook name and click on + to expand it. Within that you
should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code
pane. The workbook will be saved to c:\ with file name containing date and
time.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then
Me.SaveCopyAs "c:\Backup" & Format(Now(), "YYMMDDhhmmss") & ".xls"
End If
End Sub

If this post helps click Yes

 

[Wombat]

Doesnt seem to work... I'm using a German version of Excel. Is the
programming language different?

 

[Wombat]

Scrap the last post... I've played around with it and it works! Thanks for
your help

 

[Gord Dibben]

What if someone screws up and does not want to save the screwed-up changes
to the local drive?

You might want a "Do you want to save changes" option before closing.


Gord Dibben MS Excel MVP