Printable Phone Directory




Scenario

An national company of 2,000 staff across 40 location needed a fast and reliable way to generate a printable phone directory.

Each month an update was required to the company phone directory.  The update took several days’ of effort finding out who had started, changed positions and left the organisation. They wanted a simple process which could be run by an office administrator in minutes to create a beautiful phone directory.



Business Challenge

Each month an update was required to the company phone directory. The update took several days’ worth of effort finding out who had started, changed positions and left the organisation. They wanted a simple process which could be run by an office administrator in minutes.

Read more 



Microsoft SharePoint 2010
  • VBScript
  • Microsoft Excel
  • Microsoft Word
3 days

Approach
Luckily for us we had already been engaged on another project which cleansed the content in their Active Directory. This project setup processes to ensure all staff were created with accurate details in the correct Organisational Units. As we already had a good trusted source of data, we only had to decide on the creation approach of the phone directory.

As the process was going to be run by an office administrator we decided that Microsoft Word should be that main program from which the phone directory could be printed.

We reviewed their existing phone directory to determine what properties should be extracted from Active Directory to the printable version. The directory was laid out based on location. Each location had a customised header containing site details, emergency numbers and internal codes. Under the header the office staff were listed alphabetically displaying their preferred name, job title, office phone and mobile.

A Visual Basic Script was developed to extract all valid user accounts from Active Directory to a spreadsheet. Once in the spreadsheet the data was sorted by site and staff member for inclusion in the Word document. Lastly a Word template was created that included pre-formatted tables extracted from the spreadsheet containing the details of all staff at the site.


Outcome

The phone directory creation process is now run in minutes to generate a Word document that can be converted to PDF or printed. Staff now know AD is used to populate the phone directory. If details are wrong, this is now maintained which has added benefit of keeping linked systems up to date.