Stop an excel file from being moved or copied - do I validate filepath?

[N E Body]

Hello

I have an Excel file on a shared drive for everyone to access. Its a sort
of database where everyone keeps adding to it. Originally I set up a
shortcut on all desktops to access the file and all went well.
Lately information was missing, person A said they had entered the details
but when I accessed the file it was not there - somehow they had a copy of
the file on their desktop so the info never reached the file on the shared
drive.

How can I stop my file from being copied or moved. My best thought was to
have some sort of validation on start up, probably checking the filepath was
correct. I dont have a clue how to do this!!

Any assistance or other suggestions would be appreciated.

TIA

Kenny
using XP pro and office 2003

 

[Incidental]

Hi Kenny

You could use CurDir which will return the directory of the current
file, put it in a string and then check the string against the correct
path if it's correct continue with loading or if not show a msg to the
user and have the book close. the code below should do what you want
though you will have to alter it to your file location details.

Option Explicit
Dim MyLocation As String

Private Sub Workbook_Open()

MyLocation = CurDir

If MyLocation = "//Server/shared_Folder/excel/" Then
'load the database
Else
MsgBox "Your version is incorrect" & vbNewLine _
& "Please contact Me" & vbNewLine _
& "My database will now close", vbCritical
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End If

End Sub

hope this helps

S

 

[Tim]

In Workbook_Open:


Const FILE_LOC as String = "\\the\path\to\file.xls"

If ThisWorkbook.Path <> FILE_LOC Then
MsgBox "Please use only the file located at " & FILE_LOC
End if


Tim