Welcome‎ > ‎Scripting‎ > ‎

Powershell Script phone numbers Active Directory

posted Nov 22, 2010, 6:49 AM by Jurien Bosman   [ updated Nov 23, 2010, 12:50 AM ]
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
Comments