saving an excel file overwrites a file elsewhere??

[shogun assassin]

hello!
I have an excel file that constantly gets updated located in the sales drive of my server.
I also have a copy of this excel file in a program folder that a VB prog i have made uses.
is there anyway to make it so when the file in the sales drive is updated it also overwrites the copy of the file?
i'm not sure if this is even possible!
if anyone has any ideasi' appreciate it!
many thanks, iain

[PetrH]

Hi,
make in your own folder the shortcut only instead of the copy of the file. Of course it works only untill the file is renamed...
Would it be solution?
Regards
Petr

[shogun assassin]

i had thought of that but i'm not sure my program likes opening a shortcut.
plus the reason i was using a copy of the file is that the people using the program i am making dont have access rights to the sales drive where the original file is coming from! so even if it would see/open the sortcut it wouldnt work due to the permissions!

[PetrH]

Try to use BeforeClose event. The procedure would Save a copy to predefined location and confirm overwrite meassage.
Is it possible? If so and you need more help, juste write.
Regards
Petr

[shogun assassin]

i could try that. can you point me in the right direction please?
i have never used macros before so am finding it a bit confusing!
thanks

[PetrH]

Hi, write following code to VBA of your worksheet. Notice that the code must be placed in ThisWorkbook (so not in code window of some sheet). Use your own values instead of three dots.



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sales_drive As String
Dim Backup_location As String


Sales_drive = "..."
Backup_location = "..."

If ThisWorkbook.Path = Sales_drive Then
If ThisWorkbook.Saved = False Then
ans = MsgBox("Save changes?", vbYesNo + vbExclamation, "Microsoft Excel")
If ans = vbYes Then
ThisWorkbook.Save
ThisWorkbook.SaveAs Filename:=Backup_location, ConflictResolution:=xlLocalSessionChanges
Else
ThisWorkbook.Saved = True
End If
End If
End If

End Sub

Hopefully it works. Regards
Petr

[PetrH]

Hi, I found out that we have to use the similar code for event BeforeSave too. Anyway test the above written code and if it works we could improve the routine. Regards
Petr