VBA File Sucurity Code

[Juri]

Hi All,

I'm just new on this forum and new with VBA coding as well.

I tried to secure a .xlsm file which is posted on the server as ReadOnly to avoid distribution of the file to other locations. (Copy Paste of the file to another folder is possible)

So I tried a VBA codes which does check if the path (FileFolderExists) is existing, when not it should not open the file but I can get this working prior opening the file (only as macro).

Are there any other smart VBA methods to secure the file to avoid distribution of the file to a different location?

Many thanks for your support.

Juri

[lynnnow]

One method you can use is the Environ("Username") syntax. You can make a list of users who can access the file and when the file is opened, only the authorized user can actually see the file... otherwise just terminate the code.

[dantzu]

Restricting Copy/Paste the file in another location is not possible via VBA code. The code is located within the file, and as you know a user have to open the file and enable macros in order to execute the code.
The Copy/Paste on the other side is a Windows procedure that does not require the file to be opened. Therefore the code cannot be executed.
The way you organized a filepath check is one approach. Lynnnow offered you a username check. You can ask for a password and so on......

[Juri]

Thanks for your feeback.

Well the username function is not really handy because everybody should have access (=22000 persons) and it doesn't avoid the distribution of the file and the inner content/information.

So it's purely to avoid unallowed distribution of the file as far as possible.

Of course disallowing the file to be copied elsewhere is impossible, this is clear. However run a check prior it opens could be an obstruction in unallowed distribution of the inner content/information which is hidden in the file with an excel password. However this is very easy to be cracked..

What could be a good code for checking if the fiel is at the right location?
Suggestions are really helpfull.

[Juri]

[VBA]Private Function File_Exists(ByVal sPathName As String, Optional Directory As Boolean) As Boolean
'Returns True if the passed sPathName exist
'Otherwise returns False
On Error Resume Next
sDir = "C:\Documents and Settings\cp\Desktop\Tariff Folder"
If File_Exists(sDir, True) = True Then
MsgBox "Directory " & sDir & " exist"
Else
MsgBox "Directory " & sDir & " does not exist"
Application.Quit
End If
End Function[/VBA]

[Teeroy]

You can utilise the info in the following kb article http://www.vbaexpress.com/kb/getarticle.php?kb_id=540. If the workbook isn't in the correct location Kill it.