Merge info from Active Directory to Word 2003

[colinmitton]

Is there a way of merging info from Active Directory (ms - 2003) to Word 2003 so for instance on opening a word template the persons full name is added to the document?

Ahh... and can I have the 'For Idiots' guide please, I can only really play around with code created from recording macro's!

Thanks Folks

 

[WalkerEvans]

colinmitton,

This calls for the use of the ENVIRON function. If you use ENVIRON ("username") you'll get the data you want. The rest of the code would depend on exactly where in the document you wanted this information to appear, and in what format. Since you claim your skill is limited to manipulating recorded macros (good idea, by the way - most of us started by using the recorder - and we still use it!) look back through the ones you have and find one that did an INSERT; that should give you the "leg up" you need to finish this yourself.

"A committee is a life form with six or more legs and no brain." -- L. Long

 

[colinmitton]

Great, thanks for that. I'll take a look and try and do an insert! but I may need a bit of help as its all moving very quickly for me at the moment as the bosses want it yesterday now but wont give me the money to sort it! typical I should have the title IT manager / Miracle worker

Thanks I'll keep you all updated!

 

[colinmitton]

Also how do I get a macro or code to run on opening a docuemnt / template?

 

[xlbo]

The environ("username") function will actually return the userID rather than the full name.

If you require the actual name of the person, you will need to query AD using LDAP syntax which is not particularly easy to use. This will require some knowledge of ADO queries and your AD setup. If you have neither of these, it's going to be a hard slog.

As a starter for 10, this is something I use to return some fields from AD but the name of the employee field will be dependant on your implementation of AD:

Dim lRow As Long
  
On Error Resume Next

onst ADS_SCOPE_SUBTREE = 2

sht_AD.Cells.ClearContents ' Clears sheet of data

  Set objConnection = CreateObject("ADODB.Connection")
  Set objCommand = CreateObject("ADODB.Command")
  objConnection.Provider = "ADsDSOObject"
  objConnection.Open "Active Directory Provider"
  Set objCommand.ActiveConnection = objConnection

  objCommand.Properties("Page Size") = 1000
  objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

  objCommand.CommandText = _
      "SELECT Department, Title, sAMAccountName FROM 'LDAP://dc=corphq,dc=co,dc=uk' WHERE objectCategory='user' "
        '"AND Department !=''"
  Set objRecordset = objCommand.Execute

This is for excel and returns 3 fields for all entries with a type of "User". I use this in excel and use the copyfromrecordset method to dump the data to a sheet - you would probably need to extract the value you want from the recordset object and then place it where you want in the Word doc.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[colinmitton]

Geoff,

Thanks for that, I have the problem (if you could call it that) that my AD is setup in to departments with the users in their department, and the search function wil not move down to child objects! I'm now moving away from this and trying to simplify things from my end, using the user information form to pick up the relevent name and initials for basic (blank) templates.

This helps me on my way and keeps the solution simple. I will also look at creating some basic letters using autotext as a add on function too, but when I have more time.

I think I will leave the whole AD thing and try to set up either a database with the relevent infomation on or move towards menus and dropdowns to personalise strandard letters. It would have been nice for it to automatically happen but I think I need to go one step at a time.

Thanks for your help geoff.

 

[CautionMP]

colinmitton,
One last thought before you give up on Active Directory. Try something like this to see if it will work for you.

thread705-1206085​

CMP

 

[colinmitton]

Sorry to be a bit daft but how do I use that code? I copied it from the thread, pasted in to VBA editor then tried to run it but got the Compile error "user-defined type not defined" and the first line highlighted? I'm sorry I did say I needed a Idiots guide

 

[xlbo]

As per the post from CMP:

"Add a reference to Active DS Type Library (activeds.tlb) to your VBA project, this will allow you to query Active Directory."

Tools>References....search through for the AD reference and add it

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[colinmitton]

Ahh.. see what you mean now!! That compiles fine now for another stupid question, I can run it from the VBA editor but it returns nothing in the document? I'm guessing that I need some code to run this code? Sorry again! I hate being a pain with this sort of thing.

 

[xlbo]

You would need to call:

ADsFullName

somewhere in code e.g.

sub test()
call ADsFullName
end sub



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

D'oh - What I meant to post was:

sub test()
msgbox ADsFullName
end sub


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CautionMP]

colinmitton,
You will also need to be on a domain controlled computer. if I run this from my home computer I get nothing but when I run it on my work computer it works perfect.

xlbo,
Thanks for posting an LDAP snipet. I played with it a while back and couldn't figure out the syntax.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[xlbo]

CMP - no worries - it ain't exactly intuitive. My computer nearly went out the window several times when I was trying to set it up in the 1st place!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question