Save a copy solution

[nedy_03]

Hello,

We have an excel doc that is placed in a shared folder. Is it possible that every time this excel is saved by anyone to save o copy of it in another specific location (perhaps on my pc).?

Thx,
Nedy

[L@ja]

of course.
put this to thisworkbook codepage:
[VBA]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
set act=activeworkbook
activeworkbook.saveas filename:="c:\mybackup.xls"
activeworkbook.saveas filename:=act.fullname
End Sub

[/VBA]
:-)

[nedy_03]

It does not work :-S

[mdmackillop]

Hi L@ja
Your code is crashing Excel for me. I think you're getting into an endless loop.
Simpler to save a copy
[VBA]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveCopyAs Filename:="c:\mybackup.xls"
End Sub

[/VBA]

[Bob Phillips]

Try this

[vba]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
ThisWorkbook.SaveCopyAs Filename:="C:\Backups\" & Format(Date,"yyyymmdd ") & "mybackup.xls"
ThisWorkbook.Save
End Sub
[/vba]

[nedy_03]

I don't get it ! Why it doesn't save anything? ... It work for u guys ?

[rory]

Where are you putting the code? It must go in the ThisWorkbook module.

[L@ja]

sorry, my code was really wrong :-(

[dragon]

I had similar problem recently. See thread http:\vbaexpress.com/forum/showthread.php?t=14614

The following worked for me in the end...(change the location to your archive folder)


[VBA]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sFile
Dim location As String
Application.EnableEvents = False
location = "c:\log\staff authorisations archive\"
sFile = Replace(ThisWorkbook.Name, ".xls", " Backup ") & Format(Now, "yyyymmdd hh-mm-ss")
ThisWorkbook.SaveCopyAs location & sFile & ".xls"
Application.EnableEvents = True
End Sub [/VBA]

[mdmackillop]

sorry, my code was really wrong :-(

It's called the learing process

BTW, when you run code which will trigger the Event macro, add the line
[VBA]Application.EnableEvents = False[/VBA] at the start, resetting to True at the end. This prevents the loop.

[geekgirlau]

Is it possible that every time this excel is saved by anyone to save o copy of it in another specific location (perhaps on my pc).?

Apologies if I'm having a blinding flash of the obvious here, but all your examples save the copy to C: drive. Of course if another user is opening and saving this file from a shared folder, it would save to their C: drive, not Nedy's ...

[mdmackillop]

Good point GG. So can a shared folder on my PC be accessed directly from anywhere on the network? If I have time I'll try at work.