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.
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.
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!!!!!!)
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.
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.
Public Const LDAP_addy As String = "DC=department, DC=company, DC=com"
Public Const LDAP_SO_financials As String = "CN=grp_SOfinancials, OU=MOSS2007, OU=Service Accounts, OU=Machine Services"
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.
Machine Services
-> Service Accounts
-> Moss 2007
-> grp_SOfinancials
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..) against the AD group
database_permitted = IsMemberOf()
database_permitted is then used to hide the Control Panel button on the front page of our Excel worksheet.
Function IsMemberOf() As Boolean
'Routine to check that username is a member of the group passed as a variable
Dim strGrp As IADsGroup
Dim strUser As IADsUser
Dim LDAP_full, SamUser As String
Dim UserSuccess As Boolean
UserSuccess = False
On error goto cleanup
'Connect to the AD using global LDAP string
LDAP_full = "LDAP://" & LDAP_SO_financials & ", " & LDAP_addy
Set strGrp = GetObject(LDAP_full)
For Each strUser In strGrp.Members
SamUser = UCase(strUser.Get("sAMAccountName"))
If Current_User = SamUser Then
UserSuccess = True
End If
Next
'Drop LDAP connection
Set strGrp = Nothing
Set strUser = Nothing
IsMemberOf = UserSuccess
Exit Function
cleanup:
If (Err.Number <> 0) Then
MsgBox ("An error has occured with Active Directory. You're security information cannot be confirmed!" & Err.Number)
End If
Set strGrp = Nothing
Set strUser = Nothing
IsMemberOf = UserSuccess
End Function