PDA

View Full Version : Save a copy solution



nedy_03
09-11-2007, 04:40 AM
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
09-11-2007, 04:50 AM
of course.
put this to thisworkbook codepage:

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


:-)

nedy_03
09-11-2007, 05:21 AM
It does not work :-S

mdmackillop
09-11-2007, 05:30 AM
Hi L@ja
Your code is crashing Excel for me. I think you're getting into an endless loop.
Simpler to save a copy

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveCopyAs Filename:="c:\mybackup.xls"
End Sub

Bob Phillips
09-11-2007, 05:31 AM
Try this



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

nedy_03
09-11-2007, 05:47 AM
I don't get it ! Why it doesn't save anything? ... It work for u guys ?

rory
09-11-2007, 05:53 AM
Where are you putting the code? It must go in the ThisWorkbook module.

L@ja
09-11-2007, 06:06 AM
sorry, my code was really wrong :-(

dragon
09-11-2007, 06:07 AM
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)



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

mdmackillop
09-11-2007, 11:18 AM
sorry, my code was really wrong :-(
It's called the learing process :yes

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

geekgirlau
09-11-2007, 08:32 PM
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
09-12-2007, 11:21 AM
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.