Solved: Only running MyExcel.xls on named machine?

[Ken Puls]

... i do understand that it is probably getting annoying for you now, because i know i get annoyed at work at people who dont "get" what i see as simple!

Oh, hardly. This is interesting, and I think you're doing fine with it.

...what i intend to do is put all the names captured on a worksheet in the analytical workbook so that the code will Vlookup or something like that the name of the user and either allow or deny working access to the workbook. Did that make sense?

Yes, but you may want to consider using ADO to connect to the workbook and just run an SQL (Select) query to check the current computer/user name. Means that you don't have to open the workbook in the Excel UI to levearge the vlookup. We can help you with that too.

i currently have your code for checking for authr.txt in the workbook but it is in the workbook folder, if i use thisworkbook.path and the user copies the folder for use at home will it defeat the security your code adds ?

Yes, it would. That's why I recommended using a different directory that the user wouldn't necessarily see. Now... if you are also checking their Machine name, though, then the probably would get denied, as their machine name wouldn't necessarily match.

Basically, what I'm looking at is a 3 part test. If they meet all of the criteria (Username and Computername match and the workbook is there), then let them access the file. Make sure you keep a backup of what you have, too, as you don't want someone overwriting it with an old version. (You may want to create a timestamped backup at every opening or something so you can restore if this happens.)

[Simon Lloyd]

Firstly Ken is there anyway you can mark this thread active rather than solved because it seems that i have perpetuated the lines of questioning?

I have never explored ADO or CDO or any other "DO"! and i thought SQL was SQL Server which was something different. Right now i (or should i say we!) check for the existence of the folder, the existence of the file, the authr.txt and with the collection code collect all the machine names and usernames.........Question, is it possible that when collecting 'names' we check for entry already made if it exists then dont collect the info?, it's probably me being highly lazy and not wanting to filter all the captures manually.

For my checking of a list i would probably have done

 
For Each Mycell in rng
    If Mycell.value = CN AND Mycell.OffSet(0,1)= UN Then
        Exit Sub
    Else
        Call CollectNames
    End If
Next

, I have also moved authr.txt to my own folder which is password protected (on the same drive) so i have trouble opening it from Excel because of this, it would have been nice to have it in a location they cant enter but i have moved it again to our Bin folder (for old files that we store but dont use!).

What i thought would be a small coding problem and insignificant is turning in to a monster but im enjoying learnin!, its strange the more i learn the more code i try to write!

Regards,
Simon

P.S Congrats on your new status.....one of the elite!

[Simon Lloyd]

Here's all the code i have at the moment (sorry for the length!)

 
Public Sub TestFolderExistence()
    ' Author : Ken Puls (www.excelguru.ca)
    ' Macro Purpose: Test if directory exists
    If FileFolderExists("C:\Documents and Settings\vrtSzL04\Desktop\Test", "fldr") Then
        MsgBox "Folder exists!"
    Else
       MsgBox "Folder does not exist!"
    End If
End Sub
 
Public Sub TestFileExistence()
    ' Author : Ken Puls (www.excelguru.ca)
    ' Macro Purpose: Test if directory exists
    If FileFolderExists("C:\Documents and Settings\vrtSzL04\Desktop\Test\Names Test.xls", "xls") Then
        MsgBox "File exists!"
        ' the file exists, so it is on the company drive
    Else
        MsgBox "You Can Not Work On This File Away From Work, No Changes Will Be Saved!"
        ' the file does not exist, so they must be working remotely
    End If
    Save = False
    Application.Quit
End Sub
 
Sub CollectNames()
    Dim wbDB As Workbook
    Set wbDB = Workbooks.Open("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
    With wbDB
        With .Worksheets("Sheet1")
            With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Value = Environ("ComputerName")
                .Offset(0, 1).Value = Environ("Username")
            End With
        End With
        .Close savechanges:=True
    End With
End Sub

This is the thisworkbook module

 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If FileFolderExists(ThisWorkbook.Path & Application.PathSeparator & "auth.txt", "txt") Then
        ' The file exists, so it is on the company drive
    Else
        ' The file does not exist, so they must be working remotely
        MsgBox "Sorry, but you are working away from the office. " & vbNewLine & _
        "To prevent loss of other users work, this workbook" & vbNewLine & _
        "has been restricted for use only while attached" & vbNewLine & _
        "to our corporate network.", vbCritical + vbOKOnly, "Remote Access Error"
        Cancel = True
        Saved = True
    End If
End Sub
 
Private Sub Workbook_Open()
    Dim MyCell
    Dim Rng As Range
    Application.ScreenUpdating = False
    Workbooks.Open (ThisWorkbook.Path & "\" & "Name File.xls")
    Set Rng = Sheets("Sheet1").Range("A1:B300")
    For Each MyCell In Rng
        If MyCell.Value = CN And MyCell.Offset(0, 1) = UN Then
            Exit Sub
        Else
            Call CollectNames
        End If
    Next
    ActiveWorkbook.Close ("Name File.xls")
    Application.ScreenUpdating = True
End Sub

Regards,
Simon

[mdmackillop]

Hi Simon,
Unsolved as requested.

Can you use the VBA button on your code, rather than the Code tabs in order for the formatting to be set.
Regards
MD

[Ken Puls]

Hi Simon,

I unfortunately don't have time to look at this today. I'll try to have a peek tomorrow, if that's okay.

(And thank you. )

[Simon Lloyd]

Thanks a lot Ken!, Matt sorry was just a slip as i was typing, and thanks for unsolving!
Regards,
Simon

[mdmackillop]

Matt's the other one.
Regards
Malcolm

[Switchman]

Try this code as part of a Workbook_Open event. This is a simple way to ensure it only runs on the company computers.

 
Sub Test_Enviroment
    Dim TheDomain
    TheDomain = Environ("UserDomain")
    If TheDomain = "YOUR_Active_Directory or Workgroup" Then
        GoTo ?Your label here?
    Else
        Call KillMe
    End If
End Sub
 
Sub KillMe()
    With ThisWorkbook
        .Saved = True
        .ChangeFileAccess Mode:=xlReadOnly
        Kill .FullName
        .Close False
    End With
End Sub

[Ken Puls]

Unfortunately wouldn't work for me, Switchman. We don't have workgroups or domains set up at my office as we use a Novell server to manage our directory services.

[Simon Lloyd]

Hi all, if i collect the machine names and the usernames in another workbook how would i check to see if both the names match current user and machine, ive cobbled something together but it just skips straight to msgbox "Goodbye!" even when i have stepped thru and watched it find both matches!, anyway here's my code also can i use the same code for only recording one instance of the names in my collection book instead of a For, If, Then, Next loop

 
Sub readclosedWB()
    Dim wb As Workbook
    Dim rng As Range
    Dim r, r1
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True)
    With Sheets("Sheet1")
        r = Cells.Find(What:=Environ("computername"), After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        r1 = Cells.Find(What:=Environ("username"), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        If r = Environ("computername") & r1 = Environ("username") Then
            MsgBox "Hello!"
            Exit Sub
        Else: MsgBox "Goodbye!"
            wb.Close False
            Set wb = Nothing
            Application.ScreenUpdating = True
        End If
    End With
End Sub

Regards,
Simon

[Simon Lloyd]

I have amended the code as below, the only trouble is when it comes across the first correct machine name if it checks the offset if incorrect username goes to the close procedure, what i mean is it doesn't carry on searching through the list looking for both criteria to match, any ideas?

Sub readclosedWB()
    Dim wb As Workbook
    Dim rng As Range
    Dim r
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True)
    With Sheets("Sheet1")
        r = Cells.Find(What:=Environ("computername"), After:=Range("A1"), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Select
        With ActiveWorkbook
            If ActiveCell = Environ("computername") And ActiveCell.Offset(0, 1) = Environ("username") Then
                MsgBox "Hello!" & ActiveCell.Address
                Exit Sub
            Else: MsgBox "Goodbye!"
                wb.Close False
                Set wb = Nothing
                Application.ScreenUpdating = True
            End If
        End With
    End With
End Sub

Regards,
Simon

[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.

[Charlize]

Maybe change :

With Sheets("Sheet1")

with

With wb.sheets("Sheet1")

Not sure but maybe you do a check on the wrong workbook

Charlize

ps.

Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True

must it be

Set wb = Workbooks.Open("C:\documents and settings\vrtSzL04\desktop\copy of compname.xls", True, True) 

a ) at the end ?

[Simon Lloyd]

Thanks for the response Charlize, the line you mentioned does have a ")" at the end you just needed to scroll right!, i changed the line as you suggested but it didnt make any difference to what i was trying to achieve.

Regards,
Simon

[Simon Lloyd]

Thanks Ken, a very detailed and well explained response!, i have studied the code and i don't really understand the ADO programming, however i stepped thru the code watching what was called and when and reading your comments (which helped a great deal) a couple of questions to start with (some may seem inane to you)
Questions:
1. whats the difference between a standard and class module?
2. what if the user doesn't have Access installed?
3. what does SQL stand for?
4. when stepping thru the code it looks like an array of names is being created in excel for examination and then cleared afterwards, is that correct? if so would it ever be visible in excel?i.e if an error occurred preventing the code from completing
5. once a machine name and username have been entered how do i restrict that user from recording another record in the db for himself at another machine? i.e only one entry per user!, i have had trouble with a matrix i created where people would give their login to friends or unauthorised people to access the file at their machines ( originally i used to trap usernames by hard coding the names in an IF statement for an inputbox)
6. if the db was ever deleted would that mean no-one could enter the workbook again as the wb open has the close statement if no db found?
7. if a user tries to open the file and it is currently in use it opens as read only would this cause a problem with the code or would it continue as normal?

Once again thanks for your continued support!

Regards,
Simon

[Ken Puls]

Thanks Ken, a very detailed and well explained response!, i have studied the code and i don't really understand the ADO programming, however i stepped thru the code watching what was called and when and reading your comments...

ADO essentially it can act as an interface to your database so that you can read the records, change them, etc... It actually allows you to do more, in my experience, to Access than you can through the Access user interface alone. The best way to learn is doing exactly what you're doing though.

Questions:
1. whats the difference between a standard and class module?

A class module essentially acts as a blueprint for a new object. You can define your own objects, give them properties, methods, events, etc... Honestly, this doesn't actually need to be in a class module, but once you have a class module built, you can easily export it and reuse it in other projects. What you've got is a copy of one of mine, culled down to only use the specific methods and properties we need for this.

2. what if the user doesn't have Access installed?

That's the beauty of ADO. It doesn't matter. I even have an example on my site of how to create a new Access database via ADO which will work if you don't have access on your PC.

3. what does SQL stand for?

Structured Query Language. It's an extremely powerful language that allows you to return recordsets, create/manipulate database tables and more. It's used extensively in database applications.

4. when stepping thru the code it looks like an array of names is being created in excel for examination and then cleared afterwards, is that correct? if so would it ever be visible in excel?i.e if an error occurred preventing the code from completing

Excellent! That is exactly what is happening. We request a recordset from the database, convert it to an array, and then inspect it. The array conversion is not strictly necessary, but I do it to make sure I can just dump it into a worksheet should I want to do that. If an error occured right now (with no error handling), the error message would show, but you'd never see the data in Excel. (It will only show up in a worksheet if you tell it to go there.)

5. once a machine name and username have been entered how do i restrict that user from recording another record in the db for himself at another machine? i.e only one entry per user!, i have had trouble with a matrix i created where people would give their login to friends or unauthorised people to access the file at their machines ( originally i used to trap usernames by hard coding the names in an IF statement for an inputbox)

We'll need to adjust the code a bit to do that. I'll see what I can do for you there.

6. if the db was ever deleted would that mean no-one could enter the workbook again as the wb open has the close statement if no db found?

Sot of correct. You could open the workbook with macros disabled, comment the Close line in the code, save it and reopen it. This might be a good reason to lock your VB Project if you have any other VBA'ers in your organization. For reference, the same is also true if you were using an Excel workbook as yoru database.

7. if a user tries to open the file and it is currently in use it opens as read only would this cause a problem with the code or would it continue as normal?

It won't cause any issues with the code, as it is querying the database for info. While the database can be configured for exclusive use, that is extra work that is not necessary, and therefore has not been done.

Once again thanks for your continued support!

Happy to. I'll get back to you with a mod for the user checking.

[Simon Lloyd]

Ken your title is well deserved - clear - concise explanations and responses! one question i missed out (sorry!) as we are networked (in excess of 800 machines) naturally none of us have administrator rights, therefore if a machine does not have ActiveX (as sometimes when using the web you are asked to allow ActiveX) and we are trying to use that to manipulate the db will we recieve an error or would the code just hang?, also if ActiveX is installed on the machine but is set to "ask to allow ActiveX" would we have similar problems? i had the urge to write an If statement to try to capture this but on viewing the code in the class module it looks like it would be the ADODB Connection that would have to be handled if this was the case, i wasn't sure of the language around that is it vb, vba, sql..etc.

There are only 2 other VBA'ers here that i know of and i hate to admit it but i (up until now) felt that i was the more experienced of us!

Regards,
Simon

[Ken Puls]

Ken your title is well deserved - clear - concise explanations and responses!

LOL! Well thanks.

one question i missed out (sorry!) as we are networked (in excess of 800 machines) naturally none of us have administrator rights, therefore if a machine does not have ActiveX (as sometimes when using the web you are asked to allow ActiveX) and we are trying to use that to manipulate the db will we recieve an error or would the code just hang?

Good question. I don't believe that there should be an issue here, as I've never been presented with an ActiveX prompt when doing this. The only way to know for sure, though, is to test it out.

i had the urge to write an If statement to try to capture this but on viewing the code in the class module it looks like it would be the ADODB Connection that would have to be handled if this was the case, i wasn't sure of the language around that is it vb, vba, sql..etc.

Well, if there was an issue, it would be around the ADODB connection yes. Don't overthink the languages in use here, though.

SQL is a very small portion of what we're doing. It's simply the query to extract data from the database. A one liner that is sent to the database via the ADO connection.

The ADO is just a class that can be leveraged by VB, VBA or other languages. While it makes use of arguments that need to be provided correctly, it's not really a languae per se.

As for the VB vs VBA, the two intermix. VBA is an application specific flavour of VB. When we're programming, we constantly intermix VB and VBA. If you look up items in the help files, you'll see some marked "Visual Basic".

[Simon Lloyd]

Hi....again!, ive just been reading about QI queryinterface and the pro's and cons of calling it Early Bound or Late Bound, where it explains that if we use Early binding we pass the object to the compiler first where excel is able to work out all the pointers from the class module first and so speeding things up a little and late bound seems to be the opposite, i know you haven't specified any binding properties so im confident that we dont need them, i was just thinking of the scenario of when my workbook is around 5meg and my database in excess of 2000 entries what the amount of time would be for the code execution and whether specifying Early binding would make a difference, or am i barking up the wrong tree?

 
' LATE binding 
Dim XLObj As Object 
Set XLObj = CreateObject("Excel.Application") 
' same thing with GetObject instead of CreateObject 
' EARLY binding 
Dim XLObj As Excel.Application 
Set XLObj = New Excel.Application

regards,
Simon
P.S this thread is becoming monsterous! lol

[Ken Puls]

Hi Simon,

The process of setting a reference to the library is actually early binding. You'll notice in the code that's in the file I gave you that we have:

Dim conn as ADODB.Connection

How long it will take, I'm not actually sure. It will slow down a bit, for sure, but 2000 entries shouldn't take a noticeable amount of time to search through, I wouldn't think. 20,000 maybe.