Well, it’s been a very busy week or so, I’ve really been burying my head into the Excel code thats going to form the backend of our application. Due to the nature of the data and the demands of the users, I have certain restrictions on the reference numbers that we’re using to track the items.<\/p>\n
To this end, I’ve created a back-end database sitting on the SQL server that Excel references directly using ADO to create new records, query the DB and also run a report or two.<\/p>\n
This is now in place and functioning nicely. The next step now is to tidy up the code in Excel, prior to porting it into the full Document Template that the users know and love. The key problem here is going to be the legacy code that exists within the template from a previous owner. (Note to all previous owners: DOCUMENT YOUR DAMN CODE PLEASE!!!!!!)<\/p>\n
The next step is also the thorny issue of Authentication. In order to keep the calls to LDAP down to a miniumum, I have created a start-up macro that checks for membership of aparticular AD group before the button that displays the Admin Control panel is made available.<\/p>\n
In order to make updating easier when moving from Dev to live, I’ve created a couple of constants to track the LDAP addresses.. These are concatenated within the application to created the full LDAP string.<\/p>\n
Note: The DSN details above have been changed, but basically represents the domain name department.company.com and then nested OU’s that end with a Security group.<\/p>\n Machine Services We then check the current logged on username (This is retrieved using advapi32.dll code found on the MSDN website.. e-mail me if you want more details..) <\/em> against the AD group<\/p>\n database_permitted is then used to hide the Control Panel button on the front page of our Excel worksheet.<\/p>\n On error goto cleanup<\/p>\n 'Connect to the AD using global LDAP string Set strGrp = GetObject(LDAP_full) Next<\/p>\n 'Drop LDAP connection IsMemberOf = UserSuccess<\/p>\n Exit Function If (Err.Number <> 0) Then End Function Well, it’s been a very busy week or so, I’ve really been burying my head into the Excel code thats going to form the backend of our application. Due to the nature of the data and the demands of the users, I have certain restrictions on the reference numbers that we’re using to track the … <\/p>\nPublic Const LDAP_addy As String = \"DC=department, DC=company, DC=com\"
\nPublic Const LDAP_SO_financials As String = \"CN=grp_SOfinancials, OU=MOSS2007, OU=Service Accounts, OU=Machine Services\"
\n<\/code><\/p>\n
\n -> Service Accounts
\n -> Moss 2007
\n -> grp_SOfinancials<\/p><\/blockquote>\ndatabase_permitted = IsMemberOf()<\/code><\/p>\n
\nFunction IsMemberOf() As Boolean
\n'Routine to check that username is a member of the group passed as a variable
\nDim strGrp As IADsGroup
\nDim strUser As IADsUser
\nDim LDAP_full, SamUser As String
\nDim UserSuccess As Boolean
\nUserSuccess = False<\/p>\n
\nLDAP_full = \"LDAP:\/\/\" & LDAP_SO_financials & \", \" & LDAP_addy<\/p>\n
\nFor Each strUser In strGrp.Members
\nSamUser = UCase(strUser.Get(\"sAMAccountName\"))
\nIf Current_User = SamUser Then
\nUserSuccess = True
\nEnd If<\/p>\n
\nSet strGrp = Nothing
\nSet strUser = Nothing<\/p>\n
\ncleanup:<\/p>\n
\nMsgBox (\"An error has occured with Active Directory. You're security information cannot be confirmed!\" & Err.Number)
\nEnd If
\nSet strGrp = Nothing
\nSet strUser = Nothing
\nIsMemberOf = UserSuccess<\/p>\n
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"