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
---------------
Jacob Skaria


"Wombat" wrote:

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?

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Wombat" wrote:

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?

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Wombat" wrote:

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?

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


On Wed, 28 Oct 2009 05:33:01 -0700, Wombat
wrote:

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?