Saving a file automatically everytime it is closed

  • Thread starter Thread starter Wombat
  • Start date Start date
W

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?
 
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
 
Doesnt seem to work... I'm using a German version of Excel. Is the
programming language different?
 
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
 
Back
Top