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
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
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]
:-)
L@ja
It does not work :-S
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I don't get it ! Why it doesn't save anything? ... It work for u guys ?
Where are you putting the code? It must go in the ThisWorkbook module.
Regards,
Rory
Microsoft MVP - Excel
sorry, my code was really wrong :-(
L@ja
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]
It's called the learing processOriginally Posted by L@ja
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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 ...Originally Posted by nedy_03
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'