0

I'm using some Excel files like databases.

Those files are read by a VBA code that extract data from it.

For example I've created an Excel file containing logins and password to build an authentification system.

What I would like to do now is to limit access to those files so that only my code can access/modify them.

otus
  • 385
  • 3
  • 10
  • 28
  • 2
    Unfortunately, the best advice is don't use Excel as an authentication system. You'll have to spend tons of time recreating the wheel, and when you're done, it won't be a very round wheel. You'll need a one way encryption hashing, since storing passwords as plain text is a no-no. – Tim Sands Apr 22 '15 at 14:27
  • Thanks for you answer.Actually i'm told to do so even if it's not a good practice. Can you please give more details or links about what I have to do. – otus Apr 22 '15 at 14:32

2 Answers2

0

Safeguards:

  1. Hide the window when reading, at the end of the sub close the workbook.
  2. Password protect the workbook, its VBE with a separate password.
  3. Password protect the sub that calls the workbook (containing the password as plain text).
  4. Events to every possible occasion to close the workbook if accessed manually.
  5. If someone only needs to read it, a query can be set up on the fly then disconnected ASAP.

Make sure you double- and triplecheck whether you can use alternatives, Excel is not safe.

user3819867
  • 1,114
  • 1
  • 8
  • 18
0

Instead of using Excel to store passwords, you can test who is currently logged in to windows via curr_user=Environ("USERNAME") in VBA.

This pushes the authentication to Windows and you don't have to store passwords. Then you just need to look up the curr_user on a list of authorized users, which is better than storing a list of users and passwords. @user3819867 's suggestions would still apply to this 'user list', just as it would to a 'user and password' list.

Only trade-off is that you couldn't run the Excel database on someone else's computer, but that might be a reasonable compromise compared to the alternative.

Tim Sands
  • 948
  • 4
  • 8