abhay_547
04-11-2010, 05:51 PM
Hi All,
I have created an excel addin which contains some macros but i have sent the same to all my team members but i want to restrict some of the team members from using the same .i.e who are not authorized to use the above mentioned addin. So I want a macro which should first check the existence of a user's XP ID in my sql database table and then allow him to run the macro from my addin. If the user's XP ID is not present in my sql database then it should show a message that "You are not a authorized user to run this macro". I have a code with me which actually checks the existence of Windows XP user id in Excel workbook but I want a code which should check the existence of XP id in sql server table. Following is the code :
Sub Test()
Dim ws As Worksheet
Dim strSQL As String
Dim strConnection_String As String
Dim x As String
Dim strFilePathOfAuthorizedUsersFile As String
'-----------------------------------------------------------
'NOTE: Requires reference to ADO library:
' 1. Open the Visual Basic Editor (Alt + Fll)
' 2. Choose Tools | References
' 3. Check box for Microsoft ActiveX Data Object 2.8 Library (or higher)
'-----------------------------------------------------------
'-----------------------------------------------------------
'ENTER YOUR SOURCE FILE WHERE NAMES ARE KEPT
'1) Must be an Excel file with One Column,
'2) Column header must be labeled: "NameOfAuthorizedUser"
'3) Data is a named range call "MyRange"
strFilePathOfAuthorizedUsersFile = "C:\MySourceTest.xls"
'-----------------------------------------------------------
'SQL String
strSQL = _
"SELECT NameOfAuthorizedUser " _
& "FROM myRange " _
& "WHERE " _
& "NameOfAuthorizedUser = '" & Environ("Username") & "';"
'Connection String to get data from an Excel file
strConnection_String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePathOfAuthorizedUsersFile & ";" & _
"Extended Properties=Excel 8.0;"
x = CheckForAuthorizedUser(strConnection_String, strSQL)
If x <> "" Then
MsgBox "User " & x & " Found!"
Else
MsgBox "No Authorized User Found."
End If
End Sub
Function CheckForAuthorizedUser(ByVal strConnection_String As String, ByVal strSQL As String) As String
'Creates a recordset from Excel, using filter criteria from the calling sub
'Returns a name or an empty string
'-----------------------------------------------------------
'NOTE: Requires reference to ADO library:
' 1. Open the Visual Basic Editor (Alt + Fll)
' 2. Choose Tools | References
' 3. Check box for Microsoft ActiveX Data Object 2.8 Library (or higher)
'-----------------------------------------------------------
Dim x As Long
Dim myRecordset As ADODB.Recordset
Set myRecordset = New ADODB.Recordset
'sql string - uses module-level constants
Debug.Print strSQL
'initialize recordset and run the query
Call myRecordset.Open(strSQL, strConnection_String, CursorTypeEnum.adOpenForwardOnly, _
LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
'Results
If Not myRecordset.EOF Then
CheckForAuthorizedUser = myRecordset.fields(0).Value
Else
CheckForAuthorizedUser = ""
End If
'Close recordset object and release memory
If (myRecordset.State And ObjectStateEnum.adStateOpen) Then myRecordset.Close
Set myRecordset = Nothing
End Function
Thanks for your help in advance.
I have created an excel addin which contains some macros but i have sent the same to all my team members but i want to restrict some of the team members from using the same .i.e who are not authorized to use the above mentioned addin. So I want a macro which should first check the existence of a user's XP ID in my sql database table and then allow him to run the macro from my addin. If the user's XP ID is not present in my sql database then it should show a message that "You are not a authorized user to run this macro". I have a code with me which actually checks the existence of Windows XP user id in Excel workbook but I want a code which should check the existence of XP id in sql server table. Following is the code :
Sub Test()
Dim ws As Worksheet
Dim strSQL As String
Dim strConnection_String As String
Dim x As String
Dim strFilePathOfAuthorizedUsersFile As String
'-----------------------------------------------------------
'NOTE: Requires reference to ADO library:
' 1. Open the Visual Basic Editor (Alt + Fll)
' 2. Choose Tools | References
' 3. Check box for Microsoft ActiveX Data Object 2.8 Library (or higher)
'-----------------------------------------------------------
'-----------------------------------------------------------
'ENTER YOUR SOURCE FILE WHERE NAMES ARE KEPT
'1) Must be an Excel file with One Column,
'2) Column header must be labeled: "NameOfAuthorizedUser"
'3) Data is a named range call "MyRange"
strFilePathOfAuthorizedUsersFile = "C:\MySourceTest.xls"
'-----------------------------------------------------------
'SQL String
strSQL = _
"SELECT NameOfAuthorizedUser " _
& "FROM myRange " _
& "WHERE " _
& "NameOfAuthorizedUser = '" & Environ("Username") & "';"
'Connection String to get data from an Excel file
strConnection_String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePathOfAuthorizedUsersFile & ";" & _
"Extended Properties=Excel 8.0;"
x = CheckForAuthorizedUser(strConnection_String, strSQL)
If x <> "" Then
MsgBox "User " & x & " Found!"
Else
MsgBox "No Authorized User Found."
End If
End Sub
Function CheckForAuthorizedUser(ByVal strConnection_String As String, ByVal strSQL As String) As String
'Creates a recordset from Excel, using filter criteria from the calling sub
'Returns a name or an empty string
'-----------------------------------------------------------
'NOTE: Requires reference to ADO library:
' 1. Open the Visual Basic Editor (Alt + Fll)
' 2. Choose Tools | References
' 3. Check box for Microsoft ActiveX Data Object 2.8 Library (or higher)
'-----------------------------------------------------------
Dim x As Long
Dim myRecordset As ADODB.Recordset
Set myRecordset = New ADODB.Recordset
'sql string - uses module-level constants
Debug.Print strSQL
'initialize recordset and run the query
Call myRecordset.Open(strSQL, strConnection_String, CursorTypeEnum.adOpenForwardOnly, _
LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
'Results
If Not myRecordset.EOF Then
CheckForAuthorizedUser = myRecordset.fields(0).Value
Else
CheckForAuthorizedUser = ""
End If
'Close recordset object and release memory
If (myRecordset.State And ObjectStateEnum.adStateOpen) Then myRecordset.Close
Set myRecordset = Nothing
End Function
Thanks for your help in advance.