Powershell Script phone numbers Active Directory

Post date: Nov 22, 2010 2:49:38 PM

I wrote a little script to extract phonenumbers out of our Active Directory and put them in an Excel document.

I reused most of the scripting i found on the internet, and just adjusted some to fit my own purposes.

The handy part of the script is in the tail where i want to give a cel-format to prevent excel from cutting the 0 (zero) in front of the phone number.

Also the outcome is sorted and the cellwidth is adjusted.

BTW: If you have any questions about scripting in Powershell, i might be of any help.

Here's the script:

cls

$strPath="c:\xml\test999.xls"

$a = new-object -comobject excel.application

$a.Visible = $true

if (Test-Path $strPath)

{

#Open the document

$b = $a.WorkBooks.Open($strPath)

$c = $b.Worksheets.Item(1) }

else

{ # Create It $b = $a.Workbooks.Add()

$c = $b.Worksheets.Item(1) }

$introw = 1

$users = Get-QADUser

foreach ($user in $users){

if ($user.HomePhone -gt '') {

$c.cells.item($introw, 1) = $user.LogonName

$c.cells.item($introw, 2) = $user.HomePhone

$introw++} }

$objRange = $c.UsedRange

$objRange2 = $a.Range("A1")

[void] $objRange.Sort($objRange2)

$objRange.NumberFormat = "0#########"

$objRange.EntireColumn.AutoFit() |out-null