Solved: Only running MyExcel.xls on named machine?

[Ken Puls]

Hi Simon,

I've mocked up something a little different for you. Use it or not, it's totally cool with me, but this is generally the approach that I would take.

What I'm doing here is storing the username and computername in a database, not an Excel file. We access it via ActiveX Data Objects (or ADO as it's commonly known.) You can actually use ADO to access Excel files, but I'm used to going the database route.

I've attached a sample database and and Excel file to demonstrate the usage. You'll need to do a couple of things with it though...

1) Unzip and place them somewhere. Just remember the path to "auth.mdb".
2) Open the Excel file. If you enable macros, you'll get a nice message saying you aren't authorized to use the file.
3) Go into the VBE and open up Module1. Near the top, look for the following line and modify the path to where your saved the database:

Public Const dbPath = "F:\Excel Projects in Process\auth.mdb"

4) Go to the ThisWorkbook module and uncomment the line that reads:

'        ThisWorkbook.Close savechanges:=False

5) Save the workbook and reopen it.

If you have Access, you should now be able to open the database and see your name and computer name in the tblAuth table.

Now, rename the database, and re-run the ThisWorkbook code. You should get kicked out.

Remember that the database does not need to be in the same directory as the workbook, but everyone will need access to it. The cool thing about this route is that most people won't think you are logging Excel data to a database file.

Now, a couple of important things... there is no error handling in these routines at all. You'll want to fix that before you release this in the wild. We can help you do that, but I wanted to give you something to get you started.

The workbook is currently set to log names (user and computer) to the database, so people could log multiple workstations. If that really is an issue, we'll have to modify something.

If you want to port this code to your workbook, I'd recommend exporting the class module and importing it into your existing workbook, as well as Module1. (you should probably rename that first though.) You will also need to set a reference in the VBE to the Microsoft ActiveX Data Object 2.x library as well.

Have a play and ask some questions. I'm sure you'll have a few.