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
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 ?
Where are you putting the code? It must go in the ThisWorkbook module.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.