PDA

View Full Version : Solved: Only running MyExcel.xls on named machine?



Simon Lloyd
10-11-2006, 05:08 AM
Hi all, every users machine has a unique name, is it possible in VBA to only allow your program to run on a machine with a specific name?. I have a program which we use here at work and its on a network drive but some people are taking a copy home working on it and then copying it back to the network, however if people have made changes to it in the meantime they get wiped out when the unscrupulous person who took a copy home writes back to the network.

What i would like to do is send everyone who has access to the program an excel file which when opened collects the machine name, they can then mail it back to me where i could then try to incorporate it in my VBA to only allow machines in the list.

Is it possible?

Regards,
Simon

mvidas
10-11-2006, 06:59 AM
Hi Simon,

The snippetenviron("computername")Will return the computer's name. You could put something like this at the top of your vba:if environ("computername") <> "VBAXPC" then exit sub
To collect the computer names like you described (assuming you have outlook on each machine):Sub SendComputerName()
Dim olApp As Object
Set olApp = CreateObject("outlook.application")
With olApp.CreateItem(0)
.To = "slloyd@abc.com"
.Subject = "My computer name"
.Body = Environ("computername")
.Send
End With
Set olApp = Nothing
End SubThough there should be some administrator tools to get all those names without having to deal with enabling macros, outlook security warnings, etc. However, I don't know them.

Matt

CCkfm2000
10-11-2006, 09:45 AM
thanks mvidas :thumb

just used some of the above code on my spreadsheet.

works well for what i need.

Ken Puls
10-11-2006, 10:53 AM
Hey guys,

While what you're trying to do will work, you're going to have to maintain it, which can be kind of a pain. Also, what happens when you move on, and a new user needs to be added?

The route I go with this, which involves a lot less headache, is to check for the existence of Drive H:\ on my system. Most users do not have more than a C,D or E drive at home, so this works for me. To go one step further, you could find a specific drive that everyone has access to, and drop a file in there. If the file doesn't exist, then don't let them write.

The beauty is that as long as the mapped drive doesn't change (something most IT departments try to avoid), you should be okay. Just make sure you pick one that everyone has access to, and is high enough that most users won't have it at home. If you can't, then go the file route.

I use this function (http://www.excelguru.ca/node/30) to check for either files or directories, as it works with both.

HTH,

Simon Lloyd
10-11-2006, 10:52 PM
MVidas, thanks for the detailed reply, not all machines have Outlook (as some are laptops!) is there a way Excel can collect the machine name?

Ken i understood what you are getting at, the network drive that the workbook is stored on is E:, none of our machines here have CD Rom or floppy drives (so we cant load any software!) but we do have USB's for using our companies data keys (data keys issued by the company dont work at home and vice versa), the problem of specifying a drive is that when managers or upper level staff connect their laptops the drive letter to them is no longer E: as they are fitted with all usual peripherals.

Regards,
Simon

Ken Puls
10-11-2006, 11:01 PM
Hi Simon,

Okay, how about this then... drop another file in the same directory as the workbook on your network. Just don't tell anyone about it.

You can then check for the file using:
If FileOrFolderExists(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
End If

If you felt really tricky, you could check if the file was in the directory that it is supposed to be in, or even create a file in a directory that is... say...

E:\Excel Files\TheReport.xls
E:\Confidential\MyKey.txt

By using Thisworkbook.Path, you are looking at the path where the workbook is stored. So long as you come up with something the users don't know they need to replicate, you should be fine.

Simon Lloyd
10-11-2006, 11:18 PM
Ken.....amazingly fast response!, as you will be aware we are networked and as such only have access to certain folders on certain drives, the workbook is stored in E:\Analyst\PublicFile\Raw, on this drive this is the only path i have access to as my work deals with manipulating the raw figures that users deposit, can i still use your suggestion here?, the user would be able to copy the folder with the .txt file in it.

Regards,
Simon

Simon Lloyd
10-11-2006, 11:42 PM
I can create a text file like this: Sub TextFileCreate()

Close #1
Open Application.Text(Now(), "dd mm yyyy") & ".TXT" For Output As #1

Close #1

End Sub i would just need to add the path to save it to, but i fear i can only save it to the path the folder is in on that drive!

Regards,
Simon

mvidas
10-12-2006, 06:29 AM
Simon,

As for getting a list of computer names, it is possible, but depending on the size of your company it could be a HUGE list (when I just ran it I got 4874 names)
But to get it:Sub GetComputerNames()
Dim vFF As Long, cNames() As String, Cnt As Long, tStr As String
Shell "Net view >c:\compNames.txt"
vFF = FreeFile
Open "C:\compnames.txt" For Input As #vFF
Do Until EOF(vFF)
Line Input #vFF, tStr
If Left(tStr, 2) = "\\" Then
ReDim Preserve cNames(Cnt)
cNames(Cnt) = Mid(tStr, 3, InStr(tStr, " ") - 2)
Cnt = Cnt + 1
End If
Loop
Close #vFF

'cNames now holds an array of all the computer names on the network

'for display purposes
Range("A1").Resize(Cnt, 1).Value = Application.Transpose(cNames)
End SubMatt

Ken Puls
10-12-2006, 07:16 AM
Matt, nice!

Simon, the setup you have is still workable for my suggestion as long as everyone is accessing that file from the same drive. The problem you're going to deal with is that some people are going to have an old copy floating around out there.

No need to create this by code, just make a plain old text file called "auth.txt". Drop it in the same directory as your workbook.

Go into your workbook and put this in the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If FileOrFolderExists(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
If you want to test it, rename the auth.txt file to something else, open the workbook, put some data in, and try to save it.

PS, don't forget that you'll also need to copy in the routine that I linked to on my website earlier in the thread. ;)

HTH,

Charlize
10-13-2006, 03:32 AM
Functions to retrieve username and computername. Two subs to demonstrate. Instead of a msgbox you could write the info to a sheet thats hidden so the people don't know why you want this. Everyone must open the sheet once (via workbook open you could automate the collecting of your requested info). After a week you take the spreadsheet from the network drives and hopefully everyone has granted your wishes. ---Thinking : is it possible to send an automated message to you. I know there exists something as a send command through cmd-window. Maybe by shell-command ---
Option Explicit
Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _
As Long
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
As Long
Public Function NameOfComputer()
' Returns the name of the computer
Dim ComputerName As String
Dim ComputerNameLen As Long
Dim Result As Long
ComputerNameLen = 256
ComputerName = Space(ComputerNameLen)
Result = GetComputerName(ComputerName, ComputerNameLen)
If Result <> 0 Then
NameOfComputer = Left(ComputerName, ComputerNameLen)
Else
NameOfComputer = "Unknown"
End If
End Function
Function UserName() As String
' Returns the name of the logged-in user
Dim Buffer As String * 100
Dim BuffLen As Long
BuffLen = 100
GetUserName Buffer, BuffLen
UserName = Left(Buffer, BuffLen - 1)
End Function
Sub show_computername()
Dim pc_name As String
pc_name = UCase(NameOfComputer)
MsgBox ("This computer his name : " & pc_name)
End Sub
Sub show_username()
Dim inlogname As String
inlogname = UCase(UserName)
MsgBox ("Inlogname user : " & inlogname)
End Sub
Charlize

mvidas
10-13-2006, 05:20 AM
Good point, Charlize! Might get a little annoying at times for Simon though :)
Sub SendPCName()
Shell "Net send slloyd " & Environ("computername")
End Sub Replace your network username with slloyd

Simon Lloyd
10-13-2006, 07:47 AM
Guys!........fantastic responses as ever, i have now added the code to my workbook that charlize gave (i have test driven the other code suggestions), however i like the idea of colleccting the names on a worksheet, MVIDAS the SendPCName sub you gave how do i use that? my networkname is "vrtSzL04" so would i substitute slloyd for that?

Regards,
Simon

mvidas
10-13-2006, 08:11 AM
Yep
You can test it yourself if you wanted, go to start, then run, and in the run dialog enter
net send vrtSzL04 hi

You should see what it looks like there
Matt

Simon Lloyd
10-14-2006, 07:22 PM
Matt, Charlize and Ken, I know ive marked this thread solved but just wanted to give feedback. Matt your code for collecting machine names gave an error first until i created compNames.txt in C: but then when run did not populate the .txt file or excel and i dont pretend to understand how you are polling the machines, Charlize your functions do what i would like only everytime a user logs on it records the information again, after 2 days i have multiple entries from 23 people, Ken your code is great for keeping the work locked down to only being edited on the network (as was my original problem) but collecting the names gives me greater control over who accesses the file i.e joe bloggs cant give Fred bloggs his login to access the file unless its on the same machine.

The "Net Send" command is kinda cool but is there a way of capturing the required data off it or do i have to go through cancelling 172 (yes i counted 'em) net messages again?

Still great and thorough responses.....as ever!, i'm at present trying to incorporate Kens code along with capturing Username and Machine name but still at present getting multiple hits of the same name.

Once again thanks guys!

Regards,
Simon

mvidas
10-16-2006, 05:43 AM
Matt your code for collecting machine names gave an error first until i created compNames.txt in C: but then when run did not populate the .txt file or excel and i dont pretend to understand how you are polling the machinesThe file should be created using the lineShell "Net view >c:\compNames.txt"The 'shell' part of that more or less tells excel to run that on a command line.. the "Net view" is a command to see the network (like 'net send' is used to send a message in the network), and the ">" tells excel to send the results to that file.
Do me a favor, go to Start, then Run, and once your dos window is open just type "net view" and press enter. I'm curious to see what comes up for you.


The "Net Send" command is kinda cool but is there a way of capturing the required data off it or do i have to go through cancelling 172 (yes i counted 'em) net messages again?
There is no way of capturing that message window (not an easy way), but what you just said begs the question:
Do you really want 172 computer names hard coded into your code? Seems to me it would be easier to use Ken's method.
But as I like to give different examples, are you on an exchange server and do you know your SMTP server name?
If you think you are on exchange (many companies are) but don't know the server, and go to Tools / Services, on the services tab there should be a listbox with one option of "Microsoft Exchange Server". Click that, and go to Properties. Write down the server listed there, and you could try using CDO to send a message (if its installed on the machine):Sub SendSimonComputerName()
Dim objCDO As Object
On Error Resume Next
Set objCDO = CreateObject("CDO.Message")
On Error GoTo 0
If objCDO Is Nothing Then 'cdo must not be installed on the machine
Exit Sub
End If
With objCDO
.Subject = "Computer name"
.From = "ComputerNames@domain.com"
.To = "simon.lloyd@company.com"
.TextBody = "Username: " & Environ("username") & vbCrLf & _
"Computer name: " & Environ("computername")
With .Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SmtpServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
.Send
End With
Set objCDO = Nothing
End SubReplace your real email address into the .To line, and replace "SmtpServer" with your server.

In the end, I'd say if you still want to hardcode the computer names, then try using the 'net view' again. If it doesn't work, consider Ken's method :) The above is just a workaround, you'd still have to gather the data from each email you received.

Ken Puls
10-16-2006, 08:52 AM
Another though, instead of the email...

Create a database in the same directory as the excel file. Add the message to check if the database is there. If it isn't, they get the message that they are working away from the office.

Add some code to write the values you want to the database. (I have code examples of doing this kind of thing with Access.) If the database is there, write the user's login ID and computer name to the database.

After a certain amount of time, you cut out the routine that adds credentials, and start to check the computer name and login ID against what you've logged in the database to see if they should get access. If the username is there, but the computer name doesn't match, you can tell them to go back to their desk. If the computer name is a match, but the username isn't, you can tell them that they are not authorized.

The nice thing about this route is that you don't have to deal with 172 separate users once you write the code. You let them start adding themselves for a while.

Once you cut off the add functionality, though, you could also add a routine that, if access is denied, offers to email you their credentials for inclusion. (via net send or whatever.)

Just a thought. :)

Simon Lloyd
10-16-2006, 03:54 PM
Matt, i dont have the Cmd facility on my machine (admin only) and i have no idea how our servers are set up, i did try sending "Net view" to my machines desktop but to no avail, nice suggestion with the cdo but there is that problem of manually inputting the captured data into a file.

Ken, your suggestion sounds great, thats the mix of the both things i wanted....only able to use the workbook in its network location, and only the users specified at the specified machines, is this how i would do that?
Sub CollectNames()
Dim CN, UN
Dim nRow, nRow1 As Long
nRow = Sheets("Sheet1").Range("A65336").End(xlUp).Row + 1
nRow1 = Sheets("Sheet1").Range("B65336").End(xlUp).Row + 1
Set CN = Environ("ComputerName")
Set UN = Environ("UserName")
Workbooks.Open ("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
nRow = UN
nRow1 = CN
Workbook.Save
Workbooks("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls").Close
End Subi get a type mismatch error with Set Cn...etc could you explain why?. As i have said previously im still learning along the way and sometimes its a little difficult to decipher what you guys put together...but as ever i'm trying!

"Very" my mum says!

Regards,
Simon

Ken Puls
10-16-2006, 11:25 PM
Hi Simon,

Your code is close. CN and UN are both variant strings in your case, and you're trying to set them as objects. If you want to run your code, you'd dim them as strings and just assign the values with them. No set statements:

Sub CollectNames()
Dim CN As String, UN As String
Dim nRow As Long, nRow1 As Long
nRow = Sheets("Sheet1").Range("A65336").End(xlUp).Row + 1
nRow1 = Sheets("Sheet1").Range("B65336").End(xlUp).Row + 1
CN = Environ("ComputerName")
UN = Environ("UserName")
Workbooks.Open ("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
Workbooks("Names Test.xls").Worksheets("Sheet1").Range("A" & nRow) = UN
Workbooks("Names Test.xls").Worksheets("Sheet1").Range("A" & nRow1) = CN
Workbook.Save
Workbooks("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls").Close
End Sub

I didn't test the above, but it shoudl work. Personally, though I'd go with something like this. Same functionality, but looks a little more elegant. :)

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

HTH,

Simon Lloyd
10-17-2006, 10:06 AM
Thanks Ken, very neat and a lot easier to read than mine!, i have put the code in to a workbook on the network that users dump raw data into, the beauty of this is only the real users would use this (so i can capture them), my analytical workbook is the one i want to protect to only use on the network and only by people on a list, 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? 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 ? i.e ThisWorkBook.Path would then be C:\.

Thanks for your continued support i this Ken, 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!

Regards,
Simon

Ken Puls
10-17-2006, 10:19 AM
... 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
10-17-2006, 11:17 AM
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
10-17-2006, 12:23 PM
Here's all the code i have at the moment (sorry for the length!)

Public Sub TestFolderExistence()
'Author : Ken Puls (www.excelguru.ca (http://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 (http://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
10-17-2006, 01:10 PM
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
10-17-2006, 01:32 PM
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
10-17-2006, 02:17 PM
Thanks a lot Ken!, Matt sorry was just a slip as i was typing, and thanks for unsolving!
Regards,
Simon

mdmackillop
10-17-2006, 02:44 PM
Matt's the other one.
Regards
Malcolm

Switchman
10-17-2006, 04:08 PM
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
10-18-2006, 01:09 PM
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
10-19-2006, 07:52 AM
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
10-19-2006, 12:52 PM
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 SubRegards,
Simon

Ken Puls
10-19-2006, 11:04 PM
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
10-20-2006, 12:47 AM
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
10-20-2006, 01:39 AM
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
10-20-2006, 01:52 AM
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
10-20-2006, 10:29 AM
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
10-20-2006, 11:36 AM
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
10-20-2006, 11:55 AM
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
10-20-2006, 01:45 PM
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
10-20-2006, 01:54 PM
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. :)

Ken Puls
10-20-2006, 02:19 PM
All righty...

Here's another version of the Excel workbook. I've changed the code around a bit to allow you to check if someone is authorized to run, or where their issue is (bad computer or user name). Just an FYI, this will only work on the first record of the database, so if you have multiple users who do use the same PC, we'll need to adjust something again.

To use this, you'll again need to go into Module1 and change the path to the database. While you're there, notice the new constant:
Public Const AddUserMode As Boolean = True
This gives you an easy way to flip the switch on adding new users. When you've got all your users in there, change the True to False, and anyone who has not been added in any way will get a nice message asking them to send you the info for inclusion. Until that time, it will add info for anyone who does not have a match of either computer or username in there.

For anyone who has a computer/user name in the database, if they don't match, they're told to go back to their seat.

Lastly, uncomment the Save line in ThisWorkbook's Workbook_Open module as well. Then save it and give it some tests. You do have Access, right? Just open up to the table, change pieces of either, close the table and reopen the workbook. You don't need to completely close the database.

Have a play, and fire away with the questions. I'm sure you'll have some. :)

Simon Lloyd
10-20-2006, 11:02 PM
Ken, yes i do have access and every other MS application (one of the privilidged few!)thats kinda cool and confusing it looks like you have set up a Case Select but is referenced in code in different modules?

Public Enum UserStatus
us_NoExist = 1
us_uNameWrong = 2
us_cNameWrong = 3
us_Authorized = 4
End Enum
does Enum have a specific job (i've never come across it)?, it seems that once you have done this it becomes available when you Dim something As. I suppose what i find confusing is that you can declare a Public Constant or Public something in one module and refer to it in other modules.

It worked brilliantly if i changed one item of captured data im told to go back to my seat, if i've changed the capture switch i'm denied access and given the information i need to send....just brilliant!

Of course you are right with the multiple machine users, its not all machines that have multiple users but there are up to 5 (no more) users for around half our machines, to capture this seems like a headache as the only way i could see of defining which user should use which machine is to find the machine they use most over a perid of time and then manualy delete the other entries, perhaps i could set up a query in access for all instances of a persons name find the most popular machine and delete the rest, of course if i get it wrong they will kick up a fuss but then i will get the required info.

As an added question: i have 27 different workbooks is it possible they can call this one first Do the checking and return them to their original workbook?, also 21 of those workbooks are shared, if a shared workbook is open by another user would it still go thru the validation process?

Regards,
Simon

Ken Puls
10-20-2006, 11:20 PM
LOL!

I did set up a select case, referencing results from function calls to other modules, yes.

With regards to the Public statement... all of your subs and functions are by default Public. I don't need to declare them with the Public keyword, but I prefer to code to make things obvious. So long as they are public, they are available to all the other modules. If you declare them as Private, though, then they are restricted to within that module only.

The Enum is a way of declaring your own constants. (Sort of like vbOkOnly is a constant). It basically lets VBA know that when you see, say "us_uNameWrong" in the code, that it's equal to 2. It can make your code a little more legible than trying to figure out what UserStatus = 2 is. In addition, if you declare your functions using the term, then it adds it to intellisense for you. So:

Function TellMeAboutThis(SomeInfo as Userstatus) as Boolean
Would allow you to have intellisense give you the list of values when you were plugging the function into code somehwere. The following (which is equivalent) would not:
Function TellMeAboutThis(SomeInfo as Long) as Boolean

Just to clarify, though, the fact that the Enum exists is what allows it to work this way, not the fact that it is Public. The Public portion just allows us to make use of it across modules. If it were a Private Enum, we'd only be able to use it in Module1 (or wherever we put it) :)

Now, with the roaming users... if you have some users that are legitimately allowed to roam, we could add a loop to check all records returned from the database. You'd need to add each workstation that they could use though. This would also benefit if you have shared desks as well.

On the 27 different workbooks... let me think on that one for a bit. There's a couple of ways that we could look at it, I'm just trying to think of the best one. ;)

johnske
10-21-2006, 12:01 AM
Why not give passwords to the legit. roamers so they can access it that way?

Ken Puls
10-21-2006, 12:05 AM
Hi John,

I think Simon's main concern is that his guys pass on their passwords to their friends. He's after a way to lock them down by user id and computer to make sure that they really are who they say they are and authorized.

Or is that not what you meant?

johnske
10-21-2006, 12:25 AM
Not quite - browsing thru this thread, we seem to have already got to the stage where user ID and/or computer is checked...

Now, we've established they're working from home, so - now ask for their authorization password to establish that they're allowed to work from home.

You could have a number of passwords, including time-limited ones that allow access for one hour, day, week (whatever) and maybe unlimited ones for the really trusted employees. (The temp passwords could also be changed regularly if you think they're being passed around :) )

Simon Lloyd
10-21-2006, 05:07 AM
Ken Thanks for the brief lesson - a few light bulbs switched on!, users really shouldnt roam all those that do need to be locked out, yes there are shared desks no more than 5 users a desk.

Johnske, you know what its like working on a network, theres your network logon and password, folder passwords, file passwords etc, it seems every turn you make you have to enter a password it gets very tiresome, our network login password has to be changed every 28 days!, through kens help and insight a way has emerged of protecting, verifying and authorising the workbook and access to it silently without much user interaction or time lag - in short it checks its you at your desk and authorised to use the program - no ifs or buts - its a controlled usage.

This thread was sparked because users were taking the workbook home on flash drives then saving back to the network the next day overwriting everyones work including mine, and because i constructed the workbooks it was my task to load the back up copy back and wait for someone to complain that their recent work had been deleted - so catching the culprit provided they had the gumption to complain - no user is allowed to take their work home - company policy anyway - the password sharing and user ID (login) sharing can be controlled using Kens method - everyone least of all me will be a happy bunny!

I do understand that you have browsed the thread and it looks as if it has gone full circle, and in the interest of brevity and Kens time would like to see it come to a swift conclusion, the thread i think has had around 329 views so along the way other folk are getting schooled just like me!.

We're nearly there.....or should i say Ken is, i could never have come so far or learnt so much without the length of this thread.

Regards,
Simon

Ken Puls
10-22-2006, 09:32 PM
Hi Simon,

A new file attached. It contains two edits.

Edit number one was to the Workbook_Open code. I forgot to add the Goto ExitPoint in the Else clause of the FileorFolderExists check. Not having it there causes an error on the home users PC, rather than a clean message.

I've also added a loop around the user/computer name check. This will look to see if any user is allowed to use the computer, fixing the sahared workstation issue. Theoretically, if someone uses more than one workstation, you could still register the other combinations in the db as well. Lots of flexibility there.

Now, lastly we need to deal with the 27 workbooks... I believe that you said you had 2000 users as well, correct?

Approach 1
My personal preference (and best practices dictation) is to put the code in a separate workbook from the data. This way if you want to update either, you can do so, without affecting the other. It also means you can have one code source to update (vs 27), and reuse the code across multiple applications.

In order to do this, we'd need to convert the control workbook to an addin, which isn't hard to do. We'd also need to put in another class module (and this would have to be a class module) to monitor the opening of workbooks, to see if they should be checked for authorization. (Every workbook opened would be checked to see if it was in a list that you'd need to build.) The problem then becomes deploying your addin and getting it installed for all the users who should be running through it.

To solve that issue, we'd need to add a really simple piece of code to each workbook checking if the addin was installed. If it wasn't, install it, and run this code to validate the computer/user combos. If it doesn't install, they're at home. There's a double level of checking then, but we could strip the "at home" check from this one.

Approach 2
The easier way in the short term would be to just copy all of this code into each of your 27 workbooks. The only problem is that if you need to maintain it, you have 27 workbooks to do. It does give you a bit more freedom by having a separate database for each application though, so that you can control who is authorized for each workbook on a more granular level.

Let me know what you think. :)

Simon Lloyd
10-22-2006, 11:42 PM
Ken i cant begin to tell you how much i appreciate your time and help on this!



Approach 1
My personal preference (and best practices dictation) is to put the code in a separate workbook from the data. This way if you want to update either, you can do so, without affecting the other. It also means you can have one code source to update (vs 27), and reuse the code across multiple applications.

This is the approach that i would prefer, however i thought it would have been a fairly simple case of Workbook.Open "F:\Controls.xls"in each workbook then in Control.xls if user is not in database (provided i have closed collection) pass the error back to the original (calling) workbook and close it. My reservation with an Add-In as you said is the installation on each persons machine, if there is an Add-In doesn't the user have control over these via the toolbar?, you know what people are like - see something that wasnt there before believe they dont need it and delete it!



I believe that you said you had 2000 users as well, correct?
Yes there is the possiblity of up to 2000 entries according to our IT dept. there are 2,783 users at our level on our local network.



Approach 2
The easier way in the short term would be to just copy all of this code into each of your 27 workbooks. The only problem is that if you need to maintain it, you have 27 workbooks to do. It does give you a bit more freedom by having a separate database for each application though, so that you can control who is authorized for each workbook on a more granular level.

This level of control would be good, however i don't relish the thought of going through every workbook adding the code setting the reference in the VBE to the Microsoft ActiveX Data Object 2.x library!.

Opening the workbook at home is indeed cleaner i didnt get the error message telling me that F:\Auth.txt could not be found.

By the way do you know it was Sunday when you posted this?, don't you rest? LOL

Regards,
Simon

Ken Puls
10-23-2006, 10:09 AM
This is the approach that i would prefer, however i thought it would have been a fairly simple case...

Unfortunately, no. It could probably be made to work along those lines, but it would be a bit messy.


My reservation with an Add-In as you said is the installation on each persons machine, if there is an Add-In doesn't the user have control over these via the toolbar?, you know what people are like - see something that wasnt there before believe they dont need it and delete it!

Actually, the add-in will not show up on the toolbar unless you tell it to. Unless you create code for it, no toolbars or menu items are created. The only place it will show is in the Add-ins dialog box. And even if a user clears it from there, so long as the code is placed in the 27 workbooks to check for it, those workbooks could just reinstall it.


This level of control would be good, however i don't relish the thought of going through every workbook adding the code setting the reference in the VBE to the Microsoft ActiveX Data Object 2.x library!

Oh, it's not that bad. You can even add a reference by code, if you like:
Sub AddReference()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library

Dim strGUID As String, theRef As Variant, i As Long

'Update the GUID you need below.
strGUID = "{00000200-0000-0010-8000-00AA006D2EA4}"

'Set to continue in case of error
On Error Resume Next

'Remove any missing references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i

'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear

'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0

'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error GoTo 0
End Sub



By the way do you know it was Sunday when you posted this?, don't you rest? LOL
LOL! This is my hobby, so no. ;)

At any rate... the addin route is still probably the best route, but adding the code to each workbook is much easier. It's up to you still. :)

Simon Lloyd
10-23-2006, 03:51 PM
Ken, its fast becoming a hobby of mine!

Evaluating what i'm trying to achieve and the maintainance i think it wil have to be the Add-In. I have currently moved all workbooks to one folder E:\Analyst\Development\Admin i thought it would be much easier than finding them spread out in different folders across the network (im going to create a sheet of hyperlinks to them for future reference when i put them all back!). So in this folder there are 28 workbooks, 27 brought back in and the controls.xls all workboks have had the passwords removed.

Do i now have to open all the workbooks and in the WorkBook Open add a reference to the Add-In or install the Add_in?

Would the Add-In be in the controls.xls and all workbooks will install from there if not already in place?

Do i write the Add-In in this format?
Function Area(Length As Double, Optional Width As Variant)
If IsMissing(Width) Then
Area = Length * Length
Else
Area = Length * Width
End If
End FunctionThen save it as Add-In (not that im entirely sure how or what to write. Then do i just call the Add-In from each workbook......if the Addin could not be found would this cause an error?

Does the user have to grant permission for the Add-In lets say if it were removed by the user and the workbook wants to re-install it?

Regards,
Simon

Simon Lloyd
10-23-2006, 06:44 PM
I have been trawling the net looking for procedures to write code and modules to workbooks programatically and this is what i have found i cant get it to run even though i have set the reference to Microsoft Extensibility Library 5.3, it doesnt seem to like any reference to VBProject!. I thought this could have been used to write to all 27 workbooks either writing the reference you mentioned above or the entire code!....................me being lazy again!
Regards,
Simon

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal ClassName As String, ByVal WindowName As String) As Long
Public Declare Function LockWindowUpdate Lib "user32" _
(ByVal hWndLock As Long) As Long
Sub AddModule()
Application.VBE.MainWindow.Visible = False'''It doesnt like this line
Dim VBComp As VBComponent
Set VBComp = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "AddInRef"
Application.Visible = True
Call AddProcedure
End Sub

Sub AddProcedure()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Dim VBEHwnd As Long
On Error GoTo ErrH:
Application.VBE.MainWindow.Visible = False
VBEHwnd = FindWindow("wndclass_desked_gsk", _
Application.VBE.MainWindow.Caption)
If VBEHwnd Then
LockWindowUpdate VBEHwnd
End If
'
' your code to write code
'
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("AddInRef").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub MyNewProcedure()" & Chr(13) & _
" Msgbox ""Here is the new procedure"" " & Chr(13) & _
"End Sub"
End With
Application.Run "MyNewProcedure"
Application.VBE.MainWindow.Visible = True
'Application.VBE.MainWindow.Visible = False
Call WBO
ErrH:
LockWindowUpdate 0&
End Sub
Sub WBO()
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, "Msgbox ""Hello World"",vbOkOnly"
End With
End Sub
Function ProcedureExists(ProcedureName As String, _
ModuleName As String) As Boolean
Dim ModuleExists
On Error Resume Next
If ModuleExists(ModuleName) = True Then
ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _
.CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc) <> 0
End If
End Function

Ken Puls
10-24-2006, 09:09 AM
Hey Simon,

Your enthusiasm is amazing!

What you're going to want to do is open each workbook (probably using fso to seach for the files in the directory), and create an event procedure in the ThisWorkbook module. Chip Peason has an article on Programming to the VBE (http://www.cpearson.com/excel/vbe.htm). (Look up the Event Procedure portion.)

Question is, what code are you going to put in there?

Simon Lloyd
10-24-2006, 10:32 AM
Yes Ken, it seems the more i learn the more i want to learn..VBA is facinating with the flexibility and control it gives you, i got that code from Chips site, like i said i cant get it to work....but what i was intending was to either load whatever finished code we come up with or create a module in each workbook that the calls the control.xls workbook to begin the check for authorisation, workstation and username, like i said i have moved all the workbooks to one folder at the moment and left shortcuts for the users in the places that they used to be, however i have had a fair few complaint because people who have shortcuts on their desktops now cannot access the files (annoying but they will have to wait until the development of this final coding is complete!).

Getting back to the create a module i probably would have done something like

workbooks = E:\Analyst\Development\Admin
For Each Workbook in Workbooks '( iknow this probably isn't the correct syntax)
Workbook.Open
Call AddModule
Next WorkBook
then the code from chips site would create the module and add our code to it, so that when the workbook is opened it then opens Control.xls and performs check if not authorised close all open workbooks.

Am i going to far with the coding of these workbooks?, it seems we're so close to adding the code but so far every time i try to be lazy by automating everything.

Reagrds,
Simon

Ken Puls
10-24-2006, 11:01 AM
Am i going to far with the coding of these workbooks?, it seems we're so close to adding the code but so far every time i try to be lazy by automating everything.

LOL!

Maybe. For the sake of the code that we will drop in there, it might be more work to write the routine to write VBA than it will be to just open, paste and close your workbooks.

I'll try to look at this today, but there is definately some changes that will need to be made to the control.xls workbook and also we'll need to create code for each workbook that:


Checks if the addin is installed
If not tries to install it (If error then user must be at home)


Honestly, I'd move the files back till you're ready to deploy it. Maybe make some copies for yourself to test. Unless you want to group them all in the same place anyway, that is.

Simon Lloyd
10-24-2006, 12:07 PM
Ok Ken taken under advisement!, i have moved the workbooks back (created hyperlinks to them, dont know why i didnt do that when i created them!), as for the code to write code i found myself chuckling your probably right......but you do know that now that i have found that code i will mess around with it at home until i totally crash my PC! LOL!.

And............THANKS!, thanks for still sticking with this!

Regards,
Simon

Simon Lloyd
10-24-2006, 12:07 PM
Ok Ken taken under advisement!, i have moved the workbooks back (created hyperlinks to them, dont know why i didnt do that when i created them!), as for the code to write code i found myself chuckling your probably right......but you do know that now that i have found that code i will mess around with it at home until i totally crash my PC! LOL!.

And............THANKS!, thanks for still sticking with this!

Regards,
Simon

Bob Phillips
10-24-2006, 12:14 PM
I'm probably jumping in where I don't understand here, but why don't you develop your code in a module, export the module, then import it into the other workbooks?

Bob Phillips
10-24-2006, 12:15 PM
Using VBA to import it of course.

Simon Lloyd
10-24-2006, 01:06 PM
Ahhh! The great "El Xid", nice to hear from you, Ken is assembling an Add In for me to get a workbook to automatically open another which performs various types of verification (as discussed in the many posts of this thread), he is also creating some code which checks for the Add In and if its not there re-install it provided the user is at work.

My posting above was me trying to be lazy and not opening all 27 workbooks and copying and pasting code in to them thats why i was lookiin at Chips code to do the job but i understand what was Ken was saying and will paste the code as he suggested.

But thanks for the suggestion.

Regards,
Simon

Ken Puls
10-24-2006, 04:29 PM
Hey Simon,

Okay, new zip attached with all the following files:
-Control.xla
-Auth.mdb
-TestBook1.xls
-TestBook2.xls

Open TestBook1.xls with macros disabled. Update the path in the ThisWorkbook module to where you'll store the xla file. (Does not have to be in the standard locale, I don't believe. Haven't tested this completely through.) Save it and reopen it.

You should now get a message about not working from home, and the workbook should close. This is a good thing. Check your addins, and a new one should be installed. Go into the code of the Control.xla and update the database path.

I've converted the Control workbook to an xla, and added the class module to monitor events. I've converted the old Workbook_Open routine to a separate re-usable routine, and made the addin to ensure that it is installed.

Once it's running, open TestBook2.xls. If you're all authorized, you're good to go, else it should kick you out. There is no code in there, it's all controlled by the Control.xla class module code.

Thing is, you need to add the ThisWorkbook code to all 27 workbooks to ensure that they kick the home users.

It's not perfect, but it should do the trick to get you started.

One issue now remains... if the user disables macros the first time, they won't get the add-in installed. We need to give you a way to ensure macros are enabled, and there's tricks for that.

Review this first, though, and ask your questions. :)

johnske
10-24-2006, 05:55 PM
Put all the workbooks that need the new ThisWorkbook procedures into a folder with the book containing the new procedure.

Put this code into a standard module in the book that contains the new procedure and run it (make sure all the other workbooks are closed as I didn't add code to check if thay're open) - this is basically a 'copy and paste' for a VBE module

Option Explicit

Sub ReplaceThisWorkbookProcedures()

Dim N As Long
Dim NewCode As String

'ThisWorkbook is the file containing the new ThisWorkbook procedures
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
NewCode = .Lines(1, .countoflines)
End With

'open all files you want the new procs in
With Application
.ScreenUpdating = False
.DisplayAlerts = False
With .FileSearch
.LookIn = ThisWorkbook.Path
.Filename = "*.xls"
If .Execute > 0 Then
For N = 1 To .FoundFiles.Count
If .FoundFiles(N) <> ThisWorkbook.FullName Then
Workbooks.Open(.FoundFiles(N)).Activate

'--------Delete old procedures and replace with new--------
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .countoflines
.InsertLines 1, NewCode
End With
'-----------------------------------------------------------------

ActiveWorkbook.Close savechanges:=True
End If
Next
End If
End With
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

Simon Lloyd
10-24-2006, 05:58 PM
Ken, thanks!

1. Confused?, how does the Control.xla monitor TestBook2? I understand that we have set the workbook name in the xla but we dont set a path, do i have to set the path for each and every workbook? does it monitor files only in its own folder?............im finding it hard to grasp that there is no code in it. Testbook1 did throw me out until i changed the path of the xla so i understand how this one works because there is code in the ThisWorkBook module.

2. Xla? im not sure what this is i assume it like a template, although it doesn't have any work sheets but i can view the code.....strange!

3. You say check my add in's well ichecked in the testbook1 just excels add ins available, in testbook2 just Excel add in's (no vba add ins in either). Where did you mean?

its a bit tougher following the flow of this one as it doesnt react the same using F8 to step through it but i see what is happening but testbook2....?

Regards,
Simon

Ken Puls
10-24-2006, 10:32 PM
LOL!

First, John, nice work! :)

Okay, Simon, here's the deal.

The xla is an Excel Add-in file, not a regular workbook any more. What happens with an xla file is that it hides all the worksheets from view. The same is true of the personal.xls workbook, which you may know. Sheets are still there, but as soon as you set the IsAddin property on the workbook to True, they cease being visible. So how do you know if your add-in is open? Well, if you coded a menu structure, the menus will still be visible. In this case, though, we have no menus. Go to Tools|Add-ins. Look for "Control". It should be checked in the list now. :)

Now, why does the add-in work with Testbook 2, even though it has no code...

Remember how I said I'd add a Class Module to monitor events? The deal is this... the new class module is linked to by the Add-ins Workbook_Open event. Once that is done, EVERY workbook that is opened triggers the event in the Class Module. It examines the name of the workbook that was opened, and if it is in our list, it fires the code to check if the user is authorized to be there.

The only reason that there is code in the TestBook1.xls is that we need to be able to check if the user is allowed to use that main workbook. That code checks if the add-in is installed. If it's not, it tries to install the add-in. If it can't the user must be at work. If it can, it triggers the add-in code. If the add-in is already installed, it was doing it's thing already.

Does that make more sense?

Simon Lloyd
10-25-2006, 12:33 AM
Hi Ken thanks for the explanation it clears it up for me....but do all the workbooks have to be in the same folder for the xla to work? does the xla file have to be open? i cant imagine every workbook on the drive its located on will be checked!

For clarity....i have to list all of my workbooks where currently testbook1 & 2 are?

When i ran testbook1 i got "Error 52" Bad filename or number...and the code stopped here.
Workbooks.Open strAddinPath
Until i changed the Auth path in the controls workbook.

And i checked both workbooks again and there wasn't a Control in Add In's in either workbook!

John thanks for that work on procedure for adding stuff to the workbooks!

Regards,
Simon

P.S if dont sound compus mentus its because i have been working nights last night and im reading this at 8:30 am so i may have more questions when i can see straight.

Bob Phillips
10-25-2006, 02:25 AM
John,

Time to stop providing solutions with FileSearch. In Excel 2007, FileSearch is gone, so for forward compatibility ...

johnske
10-25-2006, 03:34 AM
John,

Time to stop providing solutions with FileSearch. In Excel 2007, FileSearch is gone, so for forward compatibility ...Really? Oh well, this is shorter anyway.

Scrap the previous Simon and use this instead (same instructions) :)
Option Explicit

Sub ReplaceThisWorkbookProcedures()

Dim FileFound As Object
Dim NewCode As String

'ThisWorkbook is the file containing all the new ThisWorkbook procedures
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
NewCode = .Lines(1, .countoflines) '< "copy" the code
End With

'open all files you want the new procs in
With Application
.ScreenUpdating = False
.DisplayAlerts = False

For Each FileFound In CreateObject("Scripting.FileSystemObject") _
.GetFolder(ThisWorkbook.Path).Files
If Right(FileFound.Name, 4) = ".xls" _
And Not FileFound.Name = ThisWorkbook.Name Then
Workbooks.Open(FileFound).Activate

'--------Delete old procedures and replace with new--------
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .countoflines
.InsertLines 1, NewCode '< "paste" the code
End With
'-----------------------------------------------------------------

ActiveWorkbook.Close savechanges:=True
End If
Next

.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

Simon Lloyd
10-25-2006, 08:25 AM
El Xid, im probably grasping the wrong end of the stick but i dont have Xl2007 as im sure many others dont and probably wont for a while........so not wanting to take sides but i for one would still like solutions i could use comfortbly!

Regards,
Simon

John thanks for the revised code

Ken Puls
10-25-2006, 10:39 AM
Hi Ken thanks for the explanation it clears it up for me....but do all the workbooks have to be in the same folder for the xla to work?

Nope. When you place the code in the ThisWorkbook module, you need make sure that the following line points to where you are going to save the add-in file:

Private Const strAddinPath = ...

In addition, make sure that the path to the database is set in the actuall add-in.

Those 2 keys control how the file finds the add-in and the database, and they can be anywhere. They should point to a location that will always be accesible though. I can't remember... did your drive letters change? If so, we may need to make it relevant to the "ThisWorkbook.Path", but if we can avoid that, it would be better. Those paths should be able to accept UNC paths too, if the logical drive mappings (letters) are different.


does the xla file have to be open? i cant imagine every workbook on the drive its located on will be checked!
The Xla file is automagically installed when you open one of the 27 workbooks. After that, it just checks if it was instaleld. Provided Excel does not crash, the setting will be placed in the registry to register the addin, and Excel will start with it installed the next time it opens. Your users will be none the wiser, and this really shouldn't slow them down much at all.

Does it check every workbook on the drive? Heck no! That would take forever! :) What is checked is every workbook that is opened, if it's stored on the drive or not.


For clarity....i have to list all of my workbooks where currently testbook1 & 2 are?

That's correct, and that's how the test works. When a workbook is opened, the event is fired. It looks to see if the name of the opened workbook is in your list. If it's not, it just ends the event, and lets the user continue on as normal. If it is in the list, though, the credential checking starts.


When i ran testbook1 i got "Error 52" Bad filename or number...and the code stopped here.
Workbooks.Open strAddinPath
Until i changed the Auth path in the controls workbook.

Hmm... will have to look into that one. I doubt your users will see that once the path is set to the db. Shoudln't affect the home user, either, as they won't have the db, so will never get there.


And i checked both workbooks again and there wasn't a Control in Add In's in either workbook!
Methinks you didn't quite follow on that part. :) The Add-in is an Application level thing, not a workbook level thing. Go to Tools|Add-ins. Look down the list until you find "Control". It should be checked. This signifies that the add-in was correctly installed.


El Xid, im probably grasping the wrong end of the stick but i dont have Xl2007 as im sure many others dont and probably wont for a while........so not wanting to take sides but i for one would still like solutions i could use comfortbly!

RE this point. Bob was just pointing out that the method John used will not work in 2007. It will still work fine in this case. The reason though, is just to get us in the habit of using methods that will work now and forward. There's nothing worse than upgrading to a new version, and finding out that the code broke. Just imagine trying to explain all of this to someone in a help forum 3 years from now. ;) John's code will be a one timer, but it's still good practice to think of the future anyway.

Simon Lloyd
10-25-2006, 11:51 AM
Ken, i understand your point about Xl2007.....just being abit dim there!

As for the Add-Ins i mus be missing the point somewhere......on both workbooks i went to Tools | Add In's and just excel standard add ins were available, i checked VBA Add-In Manager no items, i checked Control.XLA Tools |Add In's just excels standard, i checked VBA Add In Manager no items!

I know i have probably misunderstood, so if you could explain kinda idiot fashion for me i would appreciate it!

In testbook1 and Control.xla thisworkbook module i dont have


Private Const strAddinPath = ...
Regards,
Simon

Ken Puls
10-25-2006, 12:55 PM
Sorry, Simon, my gaff. I must have already had the add-in installed when I tested, although I was sure I didn't. You can't remove an add-in from the collection easily, so it messed me up a bit.

New zip attached with a new copy of the Control.xla file. This one uses a proper method to install the add-in. I've also modified the code in TestBook1.xls a bit. Go to the ThisWorkbook module there. Right at the very top is the code that I eluded to above. That path needs to be the same as the path to your add-in (wherever you store it).

PS, update the file paths as usual. :)

johnske
10-25-2006, 02:48 PM
...RE this point. Bob was just pointing out that the method John used will not work in 2007. It will still work fine in this case. The reason though, is just to get us in the habit of using methods that will work now and forward. There's nothing worse than upgrading to a new version, and finding out that the code broke. Just imagine trying to explain all of this to someone in a help forum 3 years from now. ;) John's code will be a one timer, but it's still good practice to think of the future anyway.Ken, Simon,

It's not just the future, some ppl have 2007 now and maybe they've been Googling to find something that does what you want to do...

Regards,
John :)

Ken Puls
10-25-2006, 10:38 PM
True enough, John, true enough. :)

Oh! And Simon, btw... before you copy all that code into each workbook, remember that we still need to deal with a user who disables macros. There is a trick for doing this where we make all sheets except our "Welcome" sheet VeryHidden. Then, at workbook open, if the file is authorized, we unhide everything and hide the "Welcome" sheet.

I believe that there is some code in the KB to do this. :)

Simon Lloyd
10-25-2006, 11:15 PM
Thamks Ken.....I believe the code you refer to was posted by Johnske!

I did research it and found that, i will test out your revised code then incorporate the Macro force code.

Thanks....to both of you

Regards,
Simon

P.S as for gaffs, i've got a million of 'em!

Ken Puls
10-25-2006, 11:20 PM
Cool! Let us know how you make out with it. :)

Simon Lloyd
10-25-2006, 11:41 PM
BINGO!!!!!!!!!!!

I opened the file made the adjustments...worked great opened TestBook1 code already installed, authentication worked if xla path was wrong or Auth path was wrong, change them and everything was cool........next i went to TextBook2 (which is on the same path just one folder down, Xla is stored in F:\Kens Work\Final and Testbook2 is stored in F:\Kens Work) i opened it saw nothing..checked Add-Ins and Hey Presto! there it was checked, again change the path of Auth and i was denied access!

Brilliant!

So as long as i list the workbooks whether the Add In is already installed or not authentication takes place..........so cool!

Ken i'm going to mark this epic thread Solved, and if i ever get to visit Canada i'll look you up i think you've earned a beer or 12!

Best regards,
Simon

Here's the code i found in the kb
Johnske could you explain the use of .[A100] please, as for the rest of the code i already use something similar in my workbooks, i have a front sheet which is visible and all others are just xlHidden if they dont have macros enabled they only see the front page, i take it that xlVeryHidden means they aren't available in Format | Sheets | Unhide

Private Sub Workbook_Open()

Dim Sheet As Worksheet
'make all sheets visible
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next Sheet
'hide the prompt and go to A1 on sheet1
Sheets("Prompt").Visible = xlSheetVeryHidden
Application.Goto Sheet1.[A1], scroll:=True
'clean up
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Sheets("Prompt")
'if book is already saved, make a note of it
If ActiveWorkbook.Saved = True Then .[A100] = "Saved"

'make prompt sheet visible
Dim Sheet As Worksheet
.Visible = xlSheetVisible
'hide all other sheets
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet
'if the book is already saved, delete
'the previous note and close the book
If .[A100] = "Saved" Then
.[A100].ClearContents
ActiveWorkbook.Save
End If
'clean up
Set Sheet = Nothing
End With

End Sub

Simon Lloyd
10-25-2006, 11:45 PM
Quick question Ken, the database, can i password protect it? would it still be able to be read? I just dont want anyone who finds it to be able to change anything.

Regards,
Simon

johnske
10-26-2006, 02:47 AM
Simon,

That bit was put in because - although you may've saved the workbook, the changes made when closing it forced another "Save?" prompt when you really hadn't made any changes, that bit means you're only prompted to save if it hasn't already been saved.

Here's what the Help file says about xlSheetVeryHidden "This hides the object so that the only way for you to make it visible again is by setting this property to True (the user cannot make the object visible)."

However, if the VBA Project is unlocked the user can make it visible in the VBE window by going to "Properties" and changing it to visible there - but not if it's locked. :devil2:

Regards,
John :)

Simon Lloyd
10-26-2006, 02:53 AM
Got it!, thanks the user has no way of viewing the sheets unless coded to do so, i think i will make great use of that in my workbooks.

So the.[A100] is that VBA and are there other similar statements that have that kind of control if so where can they be seen?

Thanks,
Simon

johnske
10-26-2006, 04:37 AM
No, that's just shortcut notation i.e. [A100] = Range("A100").Value :) the pertinent bit is...

'if book is already saved, make a note of it
If ActiveWorkbook.Saved = True Then .[A100] = "Saved"

'more code here

'if it's already been saved, remove this note
If .[A100] = "Saved" Then
.[A100].ClearContents
ActiveWorkbook.Save
End If

'if it hasn't been saved you'll now get the "Save?" prompt

Simon Lloyd
10-26-2006, 04:45 AM
So does that mean that anything in A100 will be overwritten? maybe i should choose something out of the used range like 50000 as the largest sheet has 46,000 rows used.

Regards,
Simon

Bob Phillips
10-26-2006, 05:12 AM
RE this point. Bob was just pointing out that the method John used will not work in 2007. It will still work fine in this case. The reason though, is just to get us in the habit of using methods that will work now and forward. There's nothing worse than upgrading to a new version, and finding out that the code broke. Just imagine trying to explain all of this to someone in a help forum 3 years from now. ;) John's code will be a one timer, but it's still good practice to think of the future anyway.

Worse than that, some customer calls you up about some code that you wrote 3 years ago andd complains it doesn't work, and expect you to fix it under 'warranty'.

Simon Lloyd
10-26-2006, 05:28 AM
Sounds Like you have been there Xld!

As for my last post i'm afraid i was being a bit dim.....A100 is on the welcome (Prompt) sheet

Regards,
Simon

johnske
10-26-2006, 06:25 AM
There was actually a very similar question posted a few days ago about copying code to a large number of workbooks but I was too lazy to write any code for it and naturally replied along the lines "don't be lazy, it's easier to just open all the books and paste the new code..." - but in the face of two very similar requests in such a short time I thort "damn, looks like it's about time to get off my bum and do it" :)

Ken Puls
10-26-2006, 11:18 AM
Worse than that, some customer calls you up about some code that you wrote 3 years ago andd complains it doesn't work, and expect you to fix it under 'warranty'.

Another good point, Bob. :)

Simon, re database protection. In Access, you need to:
-Open the database using File|Open
-When you select the file, before you say okay, change the option options (bottom left in 2003) to "Open Exclusive"
-Go to Tools|Security|Database password and set it there

Now, in Control.xla, go into the clsDBLink class module and replace the connection string in the dbConnectStr property with this:

dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & _
";Jet OLEDB:Database Password=MyPassword"

-Change "MyPassword" to the new password.
-Save the .xla project from the VBE.

Should be good to go then.

Also, make sure you protect the project on your xla and your workbooks. You don't want anyone sleuthing them, but make sure you share the password with your manager.

Cheers!

Simon Lloyd
10-30-2006, 04:05 PM
Well Ken, i've been testing this finished product out at home (using 3 different drives) and the Control.xla is very efficient at checking every .xls that is opened regardless of drive!, only one snag.........if i remove any .xls and lets say copy it to another computer i get a pop up telling me that F:\Control.xls cannot be found blah blah! when i check the xls i moved i find that in the Add-In's Control is still checked, now the .xls that i moved is not in my list in Control.xla is there a way that if it does not exist in the .xla to uncheck the Control Add-in using the Control.xla authentication?

Other than that it works like a dream!

Regards,
Simon

Sorry for the delay in replying my broadband connection seems to have an authentication problem so i had to wait until i was at work!

Ken Puls
10-30-2006, 11:15 PM
Err... can you explain that a little more slowly? Pay careful attention to the .xls and .xla extensions too. :)

Re the checking, you bet. The .xla will check every file that is opened in Excel. Doesn't matter what drive it's from. )

mvidas
10-31-2006, 11:48 AM
Sounds to me like maybe the file being copied/saved has a reference set in VBA to your addin? You'd get the "control.xla cannot be found" message if so... just a thought.
Matt

Ken Puls
10-31-2006, 11:51 AM
Hmmm... shouldn't be. Never set one that I'm aware of...

mvidas
10-31-2006, 11:54 AM
I couldnt imagine why it would be, but who knows. Using a formula in the addin wouldnt do it either; though if it was linked (=[control.xla]sheet1!a100 ?) it would give at least a similar message..

Ken Puls
10-31-2006, 12:01 PM
I'm curious on this... seems very strange.

Simon, if you can describe what you do to replicate the issue, step by step, and the exact verbiage, that would be great.

Also, go into the VBE for the workbook, and see if you have anything in a References folder for the project. Check range A100 to see what it says as well.

If you can't find anything, could you email me a copy of your workbook? I'll PM you my email address.

Cheers!

Ken Puls
11-01-2006, 12:46 PM
Hey Simon, let's bring this back public now...

The file setup works fine if a user copies the workbook and takes it home. It can't find the addin, so bails out, giving the user a message. It also works nicely on the network when you open the workbook. It installs the addin and regsiters it for future use.

The issue is caused in the following situation:
-User connects to the network and successfully registers the addin
-User disconnects from the network
-User opens Excel and gets a nice little message that the xla cannot be found.

Being that they have users with latops that are transient, this is definately an issue. We don't really want to a) tell the users where the addin is, or b) cause an error to occur on the users machine.

Excel doesn't have any built in way to remove an addin from the collection, although I think it could be unloaded. The question is what to do here. I haven't played with the following situation to test it yet:
-Load and register the addin
-Uncheck the addin
-Remove the addin from the system (leaving it registered)
-Try and trap an error if the user opens the workbook remotely.

I'm hypotesizing that it may be best to, upon any workbook close, check if any of the files are open, and if not, uninstall the addin. It will be reinstalled via the workbook next time one is opened. The issue, of course, is the "BeforeClose" portion could be triggered then cancelled.

Thoughts, anyone?

mvidas
11-01-2006, 02:15 PM
I'll admit I haven't read through every post here, but based on the last one..
You could always (using on error resume next) set the .installed property to false of the addin, then if you wanted you could even remove it from the list via the registry
hkcu\software\microsoft\office\x.x\excel\add-in manager
At least in my version (9.0 / 2000) they are listed there. It looks like there are some user-specific ones too though, not quite as easy. I believe the ones there are the non-installed ones, and the installed are listed under software\microsoft\office\x.x\excel\options with a name of OPEN to OPENn (ie OPEN1, OPEN2). I also believe these get updated when excel opens/closes, so you might have to look under \options

Though I suppose these are just rambling thoughts, take it at face value :)

Simon Lloyd
11-01-2006, 04:58 PM
Well Guy's as you know this is way way above my head....i did kind of think that if the workbook opened isnt on my list in Control.xla then dont install the add in....if it is installed uninstall it or unload it!, i did try recording a macro where i unchecked the add in and then removed it but it showed up nothing in the VBE!

Would the installation/unloading an add in every workbook open cause problems with the network?

Regards,
Simon

Ken Puls
11-01-2006, 11:51 PM
Okay, I think I've got this licked...

I've added a test in the Control.xla file that uninstalls the add-in (but does not unregister it) every time the add-in is closed. Normally this is an issue, as it can be triggered by the user closing Excel, but when they cancel it leaves Excel open... but the add-in uninstalled. The thing is, with your setup though, that they can't open a workbook without installing the add-in. This means that if they have one open, they were already authorised. If they don't, it will check again to install the add-in.

Now, while the uninstall happens, the unregister doesn't. This isn't an issue, though, as it only throws an error when the add-in is loaded. Because our regular workbooks all have code to test for the existence of the add-in before it's opened, though, we won't get that error as we've intercepted it.

For any other users who happen along, I've attached a zip file with updated files as much as I did them, (database, xla and xls files,) although still not with John's "force macros" code inside. The add-in path and database paths need to be changed in the xls and xla files respectively. :)

So basically, Simon, unzip this file and use the new Control.xla (update the db path.) You should be set to go. :) Give it some tests and let me know.

The only time that I can see an issue happening is if the user has Excel open when they disconnect from the network. At that point they'll get the error. Monitoring that event, however, is going to need a VB coder and, most likely, a separate app that you'd need to install. (i.e. I don't think it's really feasible.)

Simon Lloyd
11-02-2006, 12:29 AM
Ken i take it you hate being beat!, you have worked tirelessly on this, and to say i appreciate it is an understatement!.

I have downloaded the file and will try it later, right now its 07:30am and its bed time!

Will post back soon.

Kind regards,
Simon

Ken Puls
11-02-2006, 09:34 AM
Ken i take it you hate being beat!

That's probably a pretty accurate statement. :)