Vista - VBScript to search LDAP from Excel function?

Hi!

I have a VBScript that is working fine from cscript.exe, but I soon as I put
it into an Excel Function, I'm always getting #NAME as result... what's
wrong, do VBScript is limited when run from Excel?

Here is the Excel function:

Function ADUserLookup(cUser As String)

Set adoCommand = CreateObject("ADODB.Command")
Set ADOConnection = CreateObject("ADODB.Connection")

ADOConnection.Provider = "ADsDSOObject"
ADOConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = ADOConnection

strBase = "<LDAP://domain.com>"
strFilter = "(&(objectCategory=person)(objectClass=user)(cn=" & cUser & "))"
strAttributes = "sAMAccountName"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

Set adoRecordset = adoCommand.Execute

If adoRecordset.EOF Then
ADUserLookup = "False"
Else
ADUserLookup = "True"
End If

End Function

I would like to use this function in a column where the fullname will come
from another colunm: =aduserlookup( a2 )

What's wrong?

Thanks.

Claude Lachapelle
Systems Administrator, MCSE

"Claude Lachapelle" <ClaudeLachapelle@xxxxxx> wrote in
message news:288FF5C2-4604-46AA-BB9E-D34F7CF8D6FC@xxxxxxYes, there are some differences. The lack of the WSCRIPT object being the
most obvious.

But, that said, your function is not, strictly speaking, completely valid
vbscript code. vbscript does not support the "as {type}" qualifier. Perhaps
VBA does (that is the macro language of the office components).

So, when you run this from cscript, what does it display for you?

See another comment just past the end of your code.
I'm not completely sure, but it seems odd to me that your ADUserLookup
routine will return either a value of "True" or "False" rather than some
useful attribute of the account. Also, depending on the syntax of your "full
name" attribute, it could be that it contains some character special to
excel. Finally, perhaps VBA misreports a syntax error (i.e. the "as string"
qualifier) by ignoring the definition of the function, thereby making the
"=functionName" reference indicate the error indirectly.

/Al

"Claude Lachapelle" <ClaudeLachapelle@xxxxxx> wrote in
message news:288FF5C2-4604-46AA-BB9E-D34F7CF8D6FC@xxxxxxVBS (the IE and WSH scripting language) is a different beast than VBA (the
scripting language with various dialects for the various Microsoft Office
applications).

The scripting help file (script56.chm) has sections that discuss VBScript
features not in Visual Basic for Applications and Visual Basic for
Applications features not in VBScript. To find these sections, use the help
file's table of contents like this:
Windows Script Technologies -> VBScript -> User's Guide -> VBScript Features
not in Visual Basic for Applications

Perhaps someone else with better knowledge of both languages can point out
your exact problem.

-Paul Randall

The function below is from Excel, this is why I'm using "as String" in the
Function declaration. BUT this is pure VBScript, and from what I'm
understanding, Excel is supporting VBA, not VBScript...

Now I have two choices; running an external VBScript which will open the
..xls file and replace what I need (looking if the user is existing in Active
Directory) OR adapt this VBScript to respect VBA rules.

Thanks.

It seem that not all

"Al Dunbar" wrote:

Where did you put the function definition ? In a module ?

What happens if you wrap the function using a "tester" sub and run it that
way ?
If there are problem that should show them clearly.

Sub Tester()
Debug.Print ADUserLookup("AUSERID")
End Sub

Tim

"Claude Lachapelle" <ClaudeLachapelle@xxxxxx> wrote in
message news:288FF5C2-4604-46AA-BB9E-D34F7CF8D6FC@xxxxxx

"Claude Lachapelle" <ClaudeLachapelle@xxxxxx> wrote in
message news:7DEE7777-10B2-4FE3-BEAB-CCB94F7E1BD2@xxxxxxHow can you say this is pure VBScript, when the function statement throws
error 800A03EE "Expected ')'" with the character count indicating this is
expected just before the "as" phrase?
One could argue that excel actually does support vbscript by exporting its
document object module. But excel doesn't just *support* VBA; it uses its
own flavour of VBA as its macro language.
Yes, that would be one option
Or, more properly, rewrite your VBA macro to achieve your goal.

/Al