I don't know much about AD and I am pretty sure that much of the info. I
do have is out of date, but...
You can get AD data using the AD scripting objects. Since you are
unfamiliar with VBA there is another barrier, but I suggest you start by
looking at
http://www.gmayor.com/installing_macro.htm
for tips on how to install macros, then try the following really simple
macro for starters. Once you have copied the macro into a module in the
VBA editor, you will need to click Tools->References, look down the list
for Active DS Type Library, and check the box.
If you happen to be working with an old version of Windows Server, you
may find that the LDAP objects used here do not work.
'----------------------
Sub getADdata1()
' Object to hold basic AD system info.
Dim objADSystemInfo As ActiveDs.ADSystemInfo
Dim strUserName As String
On Error GoTo noObject
Set objADSystemInfo = CreateObject("ADSystemInfo")
' Just get the current user name and display
' it in the Immediate Window, which you can
' enable in the View menu
strUserName = objADSystemInfo.UserName
Debug.Print "User name: " & strUserName
GoTo finish
noObject:
MsgBox "Could not access Active Directory information"
Err.Clear
finish:
Set objADSystemInfo = Nothing
End Sub
'----------------------
When you run it, the "User name" should actually be an AD identifier such as
CN=the user name,CN=Users,DC=mydomain,DC=com
If you delete the ".UserName" after objADSystemInfo in the above code
and type a "." immediately after objADSystemInfo, the VB editor's
intellisense feature should show other properties you can get from this
basic info. object.
The second example shows how you can use the "User name" to get more
information about the current user.
'----------------------
Sub getADdata2()
Dim objADSystemInfo As ActiveDs.ADSystemInfo
Dim objIADsUser As ActiveDs.IADsUser
Dim strUserName As String
' Start the same way as before
On Error GoTo noObject
Set objADSystemInfo = CreateObject("ADSystemInfo")
strUserName = objADSystemInfo.UserName
Debug.Print "User name: " & strUserName
' Now use the User Name to retrieve more User info.
Set objIADsUser = GetObject("LDAP://" & strUserName)
With objIADsUser
' missing values cause errors so we have
' to trap them
On Error GoTo noData
' ensure the cache is up to date
.GetInfo
' some examples
Debug.Print "ADsPath: " & .ADsPath
Debug.Print "Fullname: " & .FullName
Debug.Print "Phone: " & .TelephoneNumber
Debug.Print "Email: " & .EmailAddress
End With
GoTo finish
noData:
If Err.Number = -2147463155 Then
' Directory Property not available (i.e. "not found in the cache")
Err.Clear
Resume Next
Else
MsgBox "Error " & Err.Number & ":" & Err.Description
GoTo finish
End If
noObject:
MsgBox "Could not access Active Directory information"
Err.Clear
finish:
Set objIADsUser = Nothing
Set objADSystemInfo = Nothing
End Sub
'----------------------
Again, you can see what what types of data are available via this
mechanism by looking at the properties of the IADsUser object. If you
need other data, there are other mechanisms that can get it for you, but
I think you will need to start exploring the relevant objects for
yourself. (FWIW as usual, the descriptions I found in MSDN and Technet
appear to be for a different version of the objects than the one I am
looking at here).
There are several possible ways to get these values into your Word
document. One way is to set a Document variable for each piece of user
information. Then in the main body of your document, you can use {
DOCVARIABLE } fields to insert the data.
e.g....
'----------------------
Sub getADdata3()
Dim objADSystemInfo As ActiveDs.ADSystemInfo
Dim objIADsUser As ActiveDs.IADsUser
Dim strUserName As String
Dim strUserItem As String
' Start the same way as before
On Error GoTo noObject
Set objADSystemInfo = CreateObject("ADSystemInfo")
strUserName = objADSystemInfo.UserName
Set objIADsUser = GetObject("LDAP://" & strUserName)
With objIADsUser
On Error GoTo noData
.GetInfo
' call a second sub to set a documentvariable called
' FullName
setDocumentVariable CStr(.FullName), "FullName"
' and another called Department
setDocumentVariable CStr(.Department), "Department"
End With
GoTo finish
noData:
If Err.Number = -2147463155 Then
' Directory Property not available (i.e. "not found in the cache")
Err.Clear
Resume Next
Else
MsgBox "Error " & Err.Number & ":" & Err.Description
GoTo finish
End If
noObject:
MsgBox "Could not access Active Directory information"
Err.Clear
finish:
Set objIADsUser = Nothing
Set objADSystemInfo = Nothing
End Sub
Sub setDocumentVariable( _
strPropValue As String, _
strVarName As String)
If strPropValue = "" Then
' we have to use a non-blank value
' or the variable is deleted
' fortunately, Document variables are
' created if they do not exist
ActiveDocument.Variables(strVarName).Value = " "
Else
ActiveDocument.Variables(strVarName).Value _
= strPropValue
End If
End Sub
'----------------------
To insert the value of the "Department" variable in your document,
a. run the macro
b. click at the point where you want to insert the value, then
c. press ctrl-F9 to insert a pair of the special "field code braces"
(you can't just type them using the { and } on the keyboard
d. between the braces, type
DOCVARIABLE Department
so you see
{ DOCVARIABLE Department }
e. select the field and press F9 to update it. Then, if necessary,
press Alt-F9 to see the result of the field.
You need to run the macro to update the values that the DOCVARIABLE
fields will use. You need to update the DOCVARIABLE fields to see any
changes to those values. In other words, it isn't completely automatic.
If you want to run such a macro when the document opens, you can put it
in an "Auto Macro". But I suggest you find out about that stuff by
looking at the macros section on the Word MVPs site at
http://word.mvps.org
There is at least one other way you can get AD current user data into
your document, and that's to set up your document as a Mail Merge main
document that uses AD as its data source. I only mention this because
people have asked about it in the past. e.g.
a. create an empty text file in Word or Notepad, and rename it to
empty.odc . Let's say we save the .odc file in c:\a.
A .odc file is an "Office Data Connection" file that normally contains
information about the data source that you want to connect to. Word
needs a .odc (or .udl) to connect to OLE DB data sources that it doesn't
have special code to deal with. Using an empty .odc is just a trick - we
put the necessary connection information in the VBA code.
b. create a new blank Word document
c. install the following macro and run it. You will still need to make
a reference to that "Active DS Type Library" , i.e. go to
Tools->References, look down the list for Active DS Type Library, and
ensure the box is checked.
'----------------------
Sub connectToADdata1()
Dim objADSystemInfo As ActiveDs.ADSystemInfo
Dim strUserName As String
' Proceed as before
On Error GoTo noObject
Set objADSystemInfo = CreateObject("ADSystemInfo")
strUserName = objADSystemInfo.UserName
' Now use that information to open the data
' source with an LDAP query string
ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\a\empty.odc", _
Connection:="Provider=ADSDSOObject;", _
SQLStatement:="<LDAP://" & strUserName & _
">;;department,telephoneNumber;SubTree"
GoTo finish
noObject:
MsgBox "Could not access Active Directory information"
Err.Clear
finish:
Set objADSystemInfo = Nothing
End Sub
'----------------------
d. You should now have a mail merge main document attached to a data
source that has one record with two columns, department and
telephoneNumber. You can insert these fields using { MERGEFIELD } fields
in the usual way (assuming you happen to be familiar with MailMerge, of
course!) and use Word's mailmerge preview facilities to view the results.
The SQLStatement parameter of the OpenDataSource call specifies an LDAP
query in a standard four-part format using ";" to separate the parts.
There are various ways to specify this query, but the main things to
note are that the first part
<LDAP://" & strUserName & ">"
normally specifies the Active Directory you want to use. the second part
normally filters the data, e.g. saying you want the Users or a specific
User. However, in this case I have put all that info. in the first part
and left the second part empty.
The third part specifies the attributes you want to retrieve, using
their LDAP names. You'll have to look them up.
It is also possible to specify these queries using a slightly more
familiar SQL-style syntax, but I have never managed to retrieve more
than one column at a time when using that approach.
I would guess that the MailMerge approach is only really likely to be
useful if you want to produce formatted lists of user info. etc.
One final point. Attributes in AD can have many types (i.e. they are not
necessarily strings) and can also be multi-value. That means that
a. you can probably only get some attributes (single-valued attributes
with simple data types that Word understands) using the MailMerge approach
b. even with the earlier non-MailMerge approach, you will have to do
more than I have done here in order to retrieve such info.
Peter Jamieson
http://tips.pjmsn.me.uk