Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit Files via VBA not folder rights or permissions
Good morning,
Do you have any ideas as for securing a workbook based upon userid. I believe each computer contains a API, MPR or DLL file which the current user of that machines userid resides. I would like to be able limit access to workbook based upon userid listed within the workbook. Reason, the file contains sensitive information but needs to be accesible to many. So, if someone who has rights to the folder saves the file and shares it with someone else. That someone else will not have acces to the file with out me specifying them somewhere in the VBA code or WB. Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit Files via VBA not folder rights or permissions
Private Declare Function apiGetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long Sub GetUserNameTest() MsgBox fOSUserName End Sub Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX < 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = "" End If End Function -- Regards, Tom Ogilvy "Curt" wrote in message ... Good morning, Do you have any ideas as for securing a workbook based upon userid. I believe each computer contains a API, MPR or DLL file which the current user of that machines userid resides. I would like to be able limit access to workbook based upon userid listed within the workbook. Reason, the file contains sensitive information but needs to be accesible to many. So, if someone who has rights to the folder saves the file and shares it with someone else. That someone else will not have acces to the file with out me specifying them somewhere in the VBA code or WB. Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit Files via VBA not folder rights or permissions
If, I'm understanding the code corretly. This is obtaing the user name or
login, correct. How can I use this to limit access to specific users? "Tom Ogilvy" wrote: Private Declare Function apiGetUserName Lib "advapi32.dll" _ Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long Sub GetUserNameTest() MsgBox fOSUserName End Sub Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX < 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = "" End If End Function -- Regards, Tom Ogilvy "Curt" wrote in message ... Good morning, Do you have any ideas as for securing a workbook based upon userid. I believe each computer contains a API, MPR or DLL file which the current user of that machines userid resides. I would like to be able limit access to workbook based upon userid listed within the workbook. Reason, the file contains sensitive information but needs to be accesible to many. So, if someone who has rights to the folder saves the file and shares it with someone else. That someone else will not have acces to the file with out me specifying them somewhere in the VBA code or WB. Is this possible? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit Files via VBA not folder rights or permissions
If you have the username from this code, compare it to your list of
authorized users. If it doesn't match, close the workbook. this would be done in the workbook open event. Of course, this is easily defeated by disabling macros. Beyond that, you would need to approach your network administrators about file permissions and so forth. An alternative would be to store the data in a real database and use Excel as a front end, passing in a username and password to retrieve authorized data. -- Regards, Tom Ogilvy "Curt" wrote in message ... If, I'm understanding the code corretly. This is obtaing the user name or login, correct. How can I use this to limit access to specific users? "Tom Ogilvy" wrote: Private Declare Function apiGetUserName Lib "advapi32.dll" _ Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long Sub GetUserNameTest() MsgBox fOSUserName End Sub Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX < 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = "" End If End Function -- Regards, Tom Ogilvy "Curt" wrote in message ... Good morning, Do you have any ideas as for securing a workbook based upon userid. I believe each computer contains a API, MPR or DLL file which the current user of that machines userid resides. I would like to be able limit access to workbook based upon userid listed within the workbook. Reason, the file contains sensitive information but needs to be accesible to many. So, if someone who has rights to the folder saves the file and shares it with someone else. That someone else will not have acces to the file with out me specifying them somewhere in the VBA code or WB. Is this possible? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit Files via VBA not folder rights or permissions
Tom,
I would first like to apologize for all the questions. I realtively new to the VBA areana. Nevertheless, I have a list of the usernames already. How would I get VBA to compare the list of authorized users and closethe workbook? Disabling the macros, would render this feature useless. But, is it possible to make the workbook run only with macros enable or close workbook? Just thinking out load. "Tom Ogilvy" wrote: If you have the username from this code, compare it to your list of authorized users. If it doesn't match, close the workbook. this would be done in the workbook open event. Of course, this is easily defeated by disabling macros. Beyond that, you would need to approach your network administrators about file permissions and so forth. An alternative would be to store the data in a real database and use Excel as a front end, passing in a username and password to retrieve authorized data. -- Regards, Tom Ogilvy "Curt" wrote in message ... If, I'm understanding the code corretly. This is obtaing the user name or login, correct. How can I use this to limit access to specific users? "Tom Ogilvy" wrote: Private Declare Function apiGetUserName Lib "advapi32.dll" _ Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long Sub GetUserNameTest() MsgBox fOSUserName End Sub Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX < 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = "" End If End Function -- Regards, Tom Ogilvy "Curt" wrote in message ... Good morning, Do you have any ideas as for securing a workbook based upon userid. I believe each computer contains a API, MPR or DLL file which the current user of that machines userid resides. I would like to be able limit access to workbook based upon userid listed within the workbook. Reason, the file contains sensitive information but needs to be accesible to many. So, if someone who has rights to the folder saves the file and shares it with someone else. That someone else will not have acces to the file with out me specifying them somewhere in the VBA code or WB. Is this possible? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit Files via VBA not folder rights or permissions
In a standard module (insert=Module), put in code like this
Private Declare Function apiGetUserName Lib "advapi32.dll" _ Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long Public Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX < 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = "" End If End Function In the VBE, in the project explorer, double click on the ThisWorkbook Entry and in the ThisWorkbook Code module put in code like this: Private Sub Workbook_Open() Dim rng as Range Dim sStr as String Dim res as Variant sStr = fOSUserName() if sStr = "" then ThisWorkbook.Close Savechanges:=False With ThisWorkbook.Worksheets("List") set rng = .Range(.Range("A1"),.Range("A1").End(xldown)) End with res = Application.Match(sStr,rng,0) if iserror(res) then ThisWorkbook.Close SaveChanges:=False End if End Sub for insights on Events, see Chip Pearson's page on Events http://www.cpearson.com/excel/events.htm You can make it enticing for the user to enable macros, but you can't force them too. If macros are disabled, it is like asking a stump to sing - you have no control. You might actually have your data in a second password protected, hidden workbook and have the user open a dummy workbook that handles authorization - then opens the real workbook and closes itself. So if the user diables macros in the dummy workbook, they don't have access to the data. -- Regards, Tom Ogilvy "Curt" wrote in message ... Tom, I would first like to apologize for all the questions. I realtively new to the VBA areana. Nevertheless, I have a list of the usernames already. How would I get VBA to compare the list of authorized users and closethe workbook? Disabling the macros, would render this feature useless. But, is it possible to make the workbook run only with macros enable or close workbook? Just thinking out load. "Tom Ogilvy" wrote: If you have the username from this code, compare it to your list of authorized users. If it doesn't match, close the workbook. this would be done in the workbook open event. Of course, this is easily defeated by disabling macros. Beyond that, you would need to approach your network administrators about file permissions and so forth. An alternative would be to store the data in a real database and use Excel as a front end, passing in a username and password to retrieve authorized data. -- Regards, Tom Ogilvy "Curt" wrote in message ... If, I'm understanding the code corretly. This is obtaing the user name or login, correct. How can I use this to limit access to specific users? "Tom Ogilvy" wrote: Private Declare Function apiGetUserName Lib "advapi32.dll" _ Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long Sub GetUserNameTest() MsgBox fOSUserName End Sub Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX < 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = "" End If End Function -- Regards, Tom Ogilvy "Curt" wrote in message ... Good morning, Do you have any ideas as for securing a workbook based upon userid. I believe each computer contains a API, MPR or DLL file which the current user of that machines userid resides. I would like to be able limit access to workbook based upon userid listed within the workbook. Reason, the file contains sensitive information but needs to be accesible to many. So, if someone who has rights to the folder saves the file and shares it with someone else. That someone else will not have acces to the file with out me specifying them somewhere in the VBA code or WB. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
folder permissions with a macro | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
How to know which user is having permissions on the different system folder. | Excel Programming | |||
Read folder access rights | Excel Programming | |||
How do I unrestrict access to files Information Rights Mgmnt? | Excel Discussion (Misc queries) |