Save a copy solution

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:

Code:

---

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

---

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

Code:

---


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

---

Try this

Code:

---

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

---

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 :-(

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)

Code:

---

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

---

Quote:

---

Originally Posted by L@ja

sorry, my code was really wrong :-(

---

It's called the learning process :yes

BTW, when you run code which will trigger the Event macro, add the line

Code:

---

Application.EnableEvents = False

---

at the start, resetting to True at the end. This prevents the loop.

Quote:

---

Originally Posted by nedy_03

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 ...

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.