I have a developed a pretty neat Excel add-in in VBA, which goes down a column of values in a sheet and performs a domain lookup query on each value populating the cells to the right with looked up attributes. Its very helpful.
I have two outstanding issues before i can really roll it out to my site; one nice to have and one rather more important.
The nice to have is my status bar update issue:
I have used a simple label on my main userform which calculates the amount of values to go through and performs an update on the form increasing the width accordingly.
Code: Select all
'update progress bar
intPrg = intPrg + 1
UserForms(0).lblProgress.Width = Int((348 / intPrgLim) * intPrg)
UserForms(0).Repaint
The code still runs however, as when all lookups have finished both the status and the cells are all updated correctly.
I wanted to add the status bar to try and reassure users that the code was still running, as i had noticed the screen only updated for the 1st few seconds, then looked like it had hung - where in fact it was still working.
any ideas?
Secondly is a more important issue to fix:
The user is prompted for their domain logon details with which the code connects to the domain to perform the lookups:
(code snippet)
Code: Select all
' Use ADO to search Active Directory.
' Use alternate credentials.***********************************NEED TO CHECK CREDENTIALS FOR SUCCESSFUL LOGIN>>>>
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Properties("User ID") = strUser
adoConnection.Properties("Password") = strPassword
adoConnection.Properties("Encrypt Password") = True
adoConnection.Properties("ADSI Flag") = ADS_SERVER_BIND _
Or ADS_SECURE_AUTHENTICATION
adoConnection.Open "Active Directory Provider"
Debug.Print adoConnection.State
Set adoCommand.ActiveConnection = adoConnection
Debug.Print adoCommand.State
' Search OU. Use server binding.
strBase = "<LDAP://" & strServer & "/" & strDNSDomain & ">"
'start loop off now authenticated
Do
' cRange.Select
Unfortunately, both properties report the same whether the correct or incorrect password is used!
This has the result in my add-in, that if the incorrect password is put in, the script just hangs :(((
i cant roll this out to other users until both of these are addressed really, as I would like to propmt the user if the password is incorrect and hence avoid hanging application, and also keep the user reassured that the script is working :)
well, thought I would throw it out there.
Thanks
Paul