code to save backup file 2 alt file

[nickolushenry]

Is it possible to automatically save an excel backup file when the original is saved or closed out? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
example<o></o>
<o> </o>
- 1 sheet multiple users will enter data onto<o></o>
- Main Excel File is saved to "A" drive<o></o>
- Backup Excel File is saved to "B" drive<o></o>
<o> </o>
Using some vb/macro/something, would it be possible to set up the file when a user hits the save button or closes the file out, have it (automatically in the background) save a backup to drive "B" and replace the file that is on drive "B" (thus not creating multiple copies)?<o></o>
<o> </o>
Thanks to all that help out in advance!!! =)

 

[MVCA]

That helped me identify that my code was putting the file name in the path twice; however, I am still getting the access denied issue.....

 

[wigi]

Do you open up the file from that network location?

Or is another user consuming the file in the network location?

 

[wigi]

BTW, this code works perfectly for me:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Me.SaveCopyAs "C:\" & Me.Name
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Me.SaveCopyAs "C:\" & Me.Name
End Sub

Does it work for your file?

 

[MVCA]

Another user will be the consumer in that location. Oh wait...I set the file in that location to a read only file. So it wasn't let me overwrite it. So now it works but I want the file to be a read only file. Thoughts?

 

[MVCA]

NM this thing is still not working right. Oh well.

 

[wigi]

Read only by Windows or Excel?

You could delete the file (the Kill statement) and save it again.

Last step, change the settings to read only.